Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello.
I have a pivot table with multiple dimensions and and expressions.
One of the exprssions is a cumulative sum (partially working):
What I want should look like something like this:
Region | Country | Item Type | Item | Sale Date | Item ID | Category ID | Sale Value | Cumulative Field |
South America | Peru | Fruit | Apple | 01/05/2022 | 1 | 1 | 2 | 2 |
Peru | Fruit | Apple | 02/05/2022 | 2 | 1 | 4 | 6 | |
Peru | Fruit | Apple | 03/05/2022 | 3 | 1 | 6 | 12 | |
Peru | Fruit | Apple | 04/05/2022 | 4 | 1 | 8 | 20 | |
South America | Chile | Fruit | Grapes | 09/03/2022 | 5 | 1 | 7 | 7 |
Chile | Fruit | Grapes | 10/03/2022 | 6 | 1 | 5 | 12 | |
Chile | Fruit | Grapes | 11/03/2022 | 7 | 1 | 9 | 21 |
The cumulative sum restarts when the item changes.
But instead I am getting this:
Region | Country | Item Type | Item | Sale Date | Item ID | Category ID | Sale Value | Cumulative Field |
South America | Peru | Fruit | Apple | 01/05/2022 | 1 | 1 | 2 | 2 |
Peru | Fruit | Apple | 02/05/2022 | 2 | 1 | 4 | 6 | |
Peru | Fruit | Apple | 03/05/2022 | 3 | 1 | 6 | 12 | |
Peru | Fruit | Apple | 04/05/2022 | 4 | 1 | 8 | 20 | |
South America | Chile | Fruit | Grapes | 09/03/2022 | 5 | 1 | 7 | 27 |
Chile | Fruit | Grapes | 10/03/2022 | 6 | 1 | 5 | 32 | |
Chile | Fruit | Grapes | 11/03/2022 | 7 | 1 | 9 | 41 |
The cumulative sum continues to sum even with a different product.
The expression that I am using is:
RangeSum(Above(Total SUM(Sale Value), 0, RowNo( Total)))
I have changed and looked around, but I am out of ideias... can someone help please.
I will be blatantly wrong 😅 but ... have you tried to write like this?
RangeSum(Above(SUM total (Sale Value), 0, RowNo( Total)))
Try
=Aggr(RangeSum(Above(Total SUM(Sale Value), 0, RowNo( ))) Region,Country,ItemType,Item)