Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $400 before Dec 31st: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
stefano27
Contributor II
Contributor II

FABS Total in Pivot Table

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.

Labels (6)
1 Reply
stefano27
Contributor II
Contributor II
Author

stefano27_0-1658894705672.png

Sample data 

Thank you