Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
bobpants
Partner - Contributor II
Partner - Contributor II

Pivot table subtotal partial sum not calculating correctly

HI,

So I have a pivot table where I simply want the subtotal (partial sum) to be the sum of the row values.

I read a ton of posts and me & a colleague spent about 7 hours trying every form of SUM and AGGR we could think of, but simply cannot get it to total correctly.

On the attached example, Overspend for A  should sub total -60 and not -35, and Underspend for A should total 25, but come back with nothing !, similarly subtotals for B should be -43 and 153 respectively.

Can anyone please assist here with some Christmas magic ?!?!?!?

 

Thanks in advance

Bob

 

 

Labels (1)
1 Reply
sunny_talwar

Try these expressions

OverSpend

Sum(Aggr(RangeMin(Sum(Budget) - Sum(Spend), 0), Branch, Department))

UnderSpend

Sum(Aggr(RangeMax(Sum(Budget) - Sum(Spend), 0), Branch, Department))