Pivot table---cummulative--rangesum

[rdsuperlike]

I need some help with doing cumulative in the pivot table  since the full accumulation is disabled for the pivot table.

I have attached a sample file.

I could get the cumulative values using rangesum.

But i need the pivot table in a particular format. As soon as I chnage it to that format  values becomes zero.

I have attached the image too.

I want the values to be incorporated in the Test table format. i.e months as columns and all the values as a single row.

Any help is appreciated.

[sunny_talwar · Accepted solution]

Try this expression:

=RangeSum(Before(Sum(Sales),0,ColumnNo()))

Also attaching your application for reference. (PFA)

Best,

Sunny

[rdsuperlike]

Can you plz explain me why the other one didnt work and why this did?

[sunny_talwar]

‌So when a dimension is pivoted, the alternative of above/below is before/after. So in order for the pivoted thing to work for you, you needed before here. Additionally, instead of rangesumming on RowNo() you needed to do it on ColumNo() becuase RowNo() stays the same (1 in your case), but ColumnNo() changes, and that is what you want for your RangeSum() to work.

I hope that this make sense.

Best,

Sunnu

[ahmadov_10]

Hi Experts,

the formula worked fine when i need the simple cumulative sum of sales . but when i need the cumulative sum of sales for each Year it' s not worked , haw can i change or configure this formula ?

= rangesum(before(sum (sales),0,columnno())) 

for exp in Dec-2017 : sum_cumulative = 2 000 000 and in Jan-2018 :  sum culmulative = 34 000 (this is the sum of sales in Jan-2018 , so he begin a new calculation )