topic Re: Cumulative sum in pivot table in QlikView

Cumulative sum in pivot table

Mon, 23 Apr 2012 08:09:08 GMT

Hello,

I am having some problem calculating a cumulative sum in a pivot table.

I have a dynamic number of months m1, m2, m3 .... on top as column headers and a number of products in the first column.

I need to have a cumulative sum of sales by month number by product.

As the number of months m1, m2, m3 is dynamic based on the period selected i was thinking of using ColumnNo() to get the last column and do a sum using Rangesum from Column(1) to Column() using the before() function.

I somehow cant get it to work.

Any suggestions onthe syntax or other methods for doing thr same.

Looking forward to a solution!

Thanks in advance

Regards

Cumulative sum in pivot table

christophebrault — Mon, 23 Apr 2012 08:19:50 GMT

Hi,

If i well understood your problem, try this expression :

=rangesum(before(sum(Sales),0,ColumnNo()))

ColumnNo() is use for the 'count' of before function. Refer to the notice for more explaination.

Re: Cumulative sum in pivot table

d_pranskus — Mon, 23 Apr 2012 08:19:55 GMT

Actually BEFORE() function has third parameter where you can specify how many columns before you want to aggregate.

Cheers Darius

Re: Cumulative sum in pivot table

Mon, 23 Apr 2012 09:28:08 GMT

Hello Christophebrault,

Thank you for your answer. That's what i was looking.

1) But I also need to specify the starting collumn number to start the cumulative calculation( for example from column 2 as column 1 has labels).

Thanks again!

Re: Cumulative sum in pivot table

Mon, 23 Apr 2012 09:31:31 GMT

@d.pranskus in the BEFORE()

Specifying an offset greater than 1 lets you move the evaluation of expression to columns further to the left of the current column. A negative offset number will actually make the before function equivalent to a after function with the corresponding positive offset number. Specifying an offset of 0 will evaluate the expression on the current column. Recursive calls will return NULL.

In this case the collumns are dynamic. Does it still apply, or did i miss something?

Thnx