Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

RangeSum in Pivot for cumulating

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

1 Solution

Accepted Solutions
rubenmarin

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.

View solution in original post

4 Replies
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.

Not applicable
Author

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

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.

Not applicable
Author

Hey Ruben,

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

cheers,

Sebastian