Hi,
I see a weird behaviour on pivot table aggregations that I would like to understand if it is intended (I hope not )
I have a pivot table where I have a key figure X and the abs(x).
I see that abs(x) is not properly propagated in the row groupings (expandable).
See this example: The first row (1731) is the aggregate of the 5 rows underneath which are expanded.
On the blue marked row, the -87k is properly converted into +87k. However, for the aggregate calculation it seems that -87k is taken into the sum.
Correct resulting output I would expect is 1906 for abs(x).
Both aggregations use the same object set properties where the two properties are first summed and then substracted (in this example the order of $A and $B is just switched).
While I do agree that the calculated output is counter-intuitive, I would say the logic itself is correct: the rolled up lines (with multiple underlying cells) first sum up the 2 fields separately, then take the difference between the summed values, and then finally calculate the absolute value. In this process, the scenarios where 1) col A has higher value than col B, and 2) col B has higher value than col A, got “offset” and therefore the rolled up result does not equal the sum of the underlying cell results.
The only workaround I can think of is by creating another column (either in the backend or with function–the latter is not yet supported in pivot table yet I believe) to calculate the absolute difference between the 2 columns and directly summing this new column in the pivot table
Thank you @Jamie Your explanation makes (unfortunately) sense. However, if this holds true the abs aggregation would only be meaningfully applicable in “non expandable” settings. Which I wonder if it is the intended design from Workshop PD?
)
