Hello,
I've been trying to find a solution for problem below for a few hours now and can't seem to get it. It is quite simple to do in excel however it is a bit harder to accomplish in Sense.
My problem is rolling a total up in a pivot table with multiple dimensions. The lowest level dimension variance can be a negative that needs to be converted into a positive (fabs) and rolled through each of the available dimensions.
I've tried getting the absolute value of the variance column however since the higher level dimension are positive the results roll up as a 0 since i'm only trying to add the negatives. Any help will be greatly appreciated. Thank you.
if(variance<0, variance, 0)
Also tried aggr function within the set and still no luck
(sum({$}Possible Hours)-sum({$}Actual Hours)) - Variance that is negative at the lowest level dimensions. Even thou the higher level dimension is not negative it will need to sum the negative values and up. Think of an Sports league with multiple conferences and divisions and local teams. In my example some local teams have a negative variance that will need to be calculated at each level (conference, division, local team).
Thank you.
