Cumulative sum in pivot table.

[jduluc12]

Hi,

My data set looks like this

ID	Name	Item	debit	Credit	perc
1	custA	ItemA	100	20	.01
1	custA	ItemB	40	20	.07
1	custB	ItemA	30	16	.08
2	CustA	ItemC	20	8	.05
2	CustC	ItemB	30	10	.06
3	CustB	ItemA	30	15	.03
3	CustA	ItemB	20	10	.06
3	CustC	ItemC	20	10	.08

My pivot table should look like this

Id	Name	Item	(debit-credit) * perc
1	custA	ItemA	((100 -20) * .01) = 0.8
		ItemB	((40-20) * .07) =  1.4
	CustB	ItemA	((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

[vishsaggi]

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))

[jduluc12]

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?