Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try this expression:
=RangeSum(Before(Sum(Sales),0,ColumnNo()))
Also attaching your application for reference. (PFA)
Best,
Sunny
Try this expression:
=RangeSum(Before(Sum(Sales),0,ColumnNo()))
Also attaching your application for reference. (PFA)
Best,
Sunny
Can you plz explain me why the other one didnt work and why this did?
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
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 )