cumulative sum for pivot

[jduluc12]

Hi,

My dataset looks like this

ID	Name	Item	Amt	Tran	perc
1	custA	ItemA	100	1	.01
1	custA	ItemB	40	1	.07
1	custB	ItemA	30	2	.08
1	CustA	ItemB	20	2	.07
2	CustC	ItemB	30	2	.07
3	CustB	ItemA	30	1	.01
3	CustA	ItemB	20	2	.07
3	CustC	ItemC	20	1	.08

My pivot table should look like this

Dim1: ID

Dim2: Name

Dim3 : Item

Expression 1: sum({<Tran=1>}Amt)

Expression 2: sum({<Tran=2>}Amt)

Expression3: perc

Expression4: (Expression 1 - Expression 2) * perc

The Total of Expression4 has to be a cumulative total.

Id	Name	Item	Expression1	Expression2	Expression3	Expression4
1	custA	ItemA	100	40	.01	0.6
		ItemB	40	20	.07	1.4
	CustB	ItemA	0	20	.08	-1.6
	Total		140	80		0.4

and so on.

I am trying things like

if(RowNo() <>0, (sum({<Tran={1}>}[Amt])

-

sum({<Tran={2}>}fabs([Amt]))) * only(perc)

,

(rangesum(above(sum({<Tran={1}>}[Amt]) , 0, NoOfRows()))

-

rangesum(above(sum({<Tran={1}>}fabs([Amt])), 0, NoOfRows())

)) * only(perc)

)

The row value comes out fine but the Total value is null or missing.

Please help.

Thanks,

Jean

[sunny_talwar]

Did you try this

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

Updated the expression to add {} around the set modifier

[jduluc12]

I tried that but somehow it brings every value as 0.

[sunny_talwar · Accepted solution]

Had to modify your data to match the result for 1st three expressions....

LOAD * INLINE [

    ID, Name, Item, Amt, Tran, perc

    1, CustA, ItemA, 100, 1, .01

    1, CustA, ItemA, 40, 2, .01

    1, CustA, ItemB, 40, 1, .07

    1, CustA, ItemB, 20, 2, .07

    1, CustB, ItemA, 20, 2, .08

    2, CustC, ItemB, 30, 2, .07

    3, CustB, ItemA, 30, 1, .01

    3, CustA, ItemB, 20, 2, .07

    3, CustC, ItemC, 20, 1, .08

];

But see what I get

[sunny_talwar]

I copy pasted your expression for Expression 1 and Expression 2 which were missing {}... I have modified the expression... give it a shot now

[jduluc12]

My original dataset is little more complicated than this one but the structure is the same.

It is not working there and bringing 0 values.

I am looking at my data set if i am missing anything,

will update you shortly.

[sunny_talwar]

Sure