Announcements
cancel
Showing results for
Did you mean:
Creator

## cumulative sum for pivot

Hi,

My dataset looks like this

IDNameItemAmtTranperc
1custAItemA1001.01
1custAItemB401.07
1custBItemA302.08
1CustAItemB202.07
2CustCItemB302.07
3CustBItemA301.01
3CustAItemB202.07
3CustCItemC201.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.

IdNameItemExpression1Expression2Expression3Expression4
1custAItemA10040.010.6
ItemB4020.071.4
CustBItemA020.08-1.6
Total140800.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.

Thanks,

Jean

1 Solution

Accepted Solutions
MVP

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

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

6 Replies
MVP

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

Creator
Author

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

MVP

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

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

MVP

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

Creator
Author

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.

MVP

Sure

Community Browser