RangeSum in Pivot for cumulating

[#1]

Hey all,

Today i tried to create some cumulated values within a pivot table with time on the X-axis. For that purpose I used two formulas, one for actual values and one for the cumuluated values:

Actual: =sum(Wareneingangsmenge)

Cumulate: =RangeSum(Actual,Before([Actual cum.]))

Problem:

If there are values for each dimension the calculations work perfectly. However, if actual values are missing right from the beginning, then some random values will begin to pop up at a random months within the table. Those values are highlighted red in the graph below. Of course there should be zeros.

Strangely enough, those values will change if I change the label of the dimension "CostCentre"!!

Did anyone discover the same problem? What would be an alternative to calculate those values?

Thanks in advance,

Sebastian

[rubenmarin]

Hi Sebastian, before() can be tricky when there are null values, if you can upload a sample I can try to look for a solution, without a sample it's very hard.

[#3]

Hi Ruben,

luckily i got the solution to the described problem:

RangeSum(Before(sum(Wareneingangsmenge),0,RowNo()))

However, this formula doesn't work with multiple dimensions in a pivot. Attached you will find an example file, with multiple dimensions. The objective is to calculate a cumulative value with respect to Version, Leistungsart and Month. The cumulative and the actual value should both be sensitive to selections in Year and Period. Meaning, that only the values that i see should be cumulated.

I would really appreciate a solution to this problem!

cheers,

Sebastian

[rubenmarin · Accepted solution]

Hi Sebastian, I tried:

RangeSum(Before(

       sum({<Version={'IST'}>}Verbrauchsmenge)

       ,0,ColumnNo()))

Seems to work, check it because there are too many values to check and i only checked a few.

[#5]

Hey Ruben,

That small change did the job. Thank you very much!

cheers,

Sebastian