4 Replies Latest reply: Apr 23, 2012 5:31 AM by Abhinav Agarwal

# Cumulative sum in pivot table

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!

Regards

• ###### Cumulative sum in pivot table

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

Hello Christophebrault,

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

Hi

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

@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