Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor

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
Esteemed Contributor 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
Contributor

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?