Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Cumulative sum in pivot table.

Hi,

My data set looks like this

IDNameItemdebitCreditperc
1custAItemA10020.01
1custAItemB4020.07
1custBItemA3016.08
2CustAItemC208.05
2CustCItemB3010.06
3CustBItemA3015.03
3CustAItemB2010.06
3CustCItemC2010.08

My pivot table should look like this

IdNameItem(debit-credit) * perc
1custAItemA((100 -20) * .01) = 0.8
ItemB((40-20) * .07) =  1.4
CustBItemA ((30-16) * .08) = 1.12
Total((100 -20) * .01) + ((40-20) * .07) + ((30-16) * .08)=  0.8+ 1.4 + 1.12 = 3.32

and so on.

it is not a simple sum((debit-credit) * perc).

It requires, may rangesum and aggr.

I am trying but i am not able to get the right expression.

Please help.

Thanks,

Jean

Tags (1)
2 Replies
Highlighted
Champion III
Champion III

Re: Cumulative sum in pivot table.

Try this as your expr in your pivot and in presentation tab check Show Partial sums for the dimensions ID and Name.

= Sum(Aggr(Sum(debit - Credit) * perc, ID, Name, Item))

Capture.PNG

Highlighted
Creator
Creator

Re: Cumulative sum in pivot table.

do you think this can also work out

= Sum(Aggr((Sum({<Tran={1}>}debit) - Sum({<Tran={2}>}Credit) )* perc, ID, Name, Item))

If we add Tran as one column?