Rounding
Hi,
For our BOARD reports we want to have the number in thousands. This is easy with the "divide" functionality. However this gives incorrect results due to roundings.
In order to solve the rounding issue I though to create a procedure to solve it (see below). However, the last column of the table highlighted in Yellow still shows 427,876 where it should actually be 514484-/-86.607 = 427,877. Which means the calculation is just incorrect.
Does someone has the same experience?
Thanks!
Accepted Answer
-
Hello Thomas,
Does it solve when you change the expression to ROUND(a/1000,0) ?
Also you can use the ROUND function as an algorithm on the dataview, so you don't necessarily need to pre-calculate the values
Kind regards
Philipp
2
Answers
-
Hello Thomas,
I think the "problem" is due to Cube Dimensions and number of value based cells / combinations.
Are you awar, in Front End (DataView, Chart, ..) your Calculation is based to the defined Axis inside the Layout? Technical you will see the sum according your selection and the used axis and on this value the calculation is executed.
Performing a DataFlow, the calculation runs for each single Cube Cell with values on Leave Level.
Using Decimals often brings this "effect" because of the Leave Level Calculation. You are calculating on most detaillized stage and not on aggregated one.
Finally both calculations should be correct.
Hope it helps a little.
regards
Alex
1 -
@Philipp Schumacher thank you for your reply. Unfortunately, with the suggested formula it will first divide the number by 1.000 and then round it. This will lead to significant difference with actuals as it rounds on thousands.
0 -
Did you try to change the total of the column to "total" instead of "calculated". In my opinion the algorithm should then work correctly and sum up both rounded values instead of rounding the total itself.
0 -
@Alexander Kappes thank you for your reply!
I'm indeed aware that the front end looks at the values how they exist in the cubes. Hence, I tried to save the values rounded and divided by thousands in another cube to avoid this issue. The detailed result in the cube after rounding and dividing looks good.
I only don't understand why the calculation is still not in line with the above numbers. It makes sense for the numbers that are not rounded like in Column "Period Prior Year". But, the calculation of the numbers (marked in yellow) which have been rounded, divided by 1.000 and are saved in the new cube leave me puzzled.
Normally I would easily accept an rounding of 1K, put unfortunately our auditors don't share my level of acceptance.
0 -
@Bettina Clausen thank you for your reply! This sounds like something that could be the solution. I'm only not sure where to find the solution. Would you be able to guide me where I can find the settings? Currently, I'm only able to find the below setting.
0 -
Hi Thomas,
that's exactly what I meant but only when using the algorithm with the round-function. When you are using a cube this won't work since values are pre-calculated based on the cell-level as Alexander has mentioned.
Kind regards,
Bettina0 -
@Bettina Clausen thank you for the swift reply.
I think I'm starting to understand where you are going at. I have build it in column test, but unfortunately it gives me the same total value as with the cube solution unfortunately.
0 -
@Philipp Schumacher I'm not sure what happened in my previous test, but your suggested formula works like a charm! Thank you very much for providing the solution!
0
