topic Re: RangeSum in Pivot for cumulating in QlikView

RangeSum in Pivot for cumulating

Mon, 16 Mar 2015 13:40:14 GMT

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

Re: RangeSum in Pivot for cumulating

rubenmarin — Mon, 16 Mar 2015 14:48:32 GMT

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.

Re: RangeSum in Pivot for cumulating

Tue, 17 Mar 2015 15:22:05 GMT

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

Re: RangeSum in Pivot for cumulating

rubenmarin — Tue, 17 Mar 2015 15:27:09 GMT

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.

Re: RangeSum in Pivot for cumulating

Fri, 20 Mar 2015 11:05:17 GMT

Hey Ruben,

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

cheers,

Sebastian