topic Re: Pivot table---cummulative--rangesum in QlikView

Pivot table---cummulative--rangesum

rdsuperlike — Mon, 11 May 2015 17:25:45 GMT

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.

Re: Pivot table---cummulative--rangesum

sunny_talwar — Mon, 11 May 2015 17:38:24 GMT

Try this expression:

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

Also attaching your application for reference. (PFA)

Best,

Sunny

Re: Pivot table---cummulative--rangesum

rdsuperlike — Mon, 11 May 2015 18:11:48 GMT

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

Re: Pivot table---cummulative--rangesum

sunny_talwar — Mon, 11 May 2015 18:57:07 GMT

‌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

Re: Pivot table---cummulative--rangesum

ahmadov_10 — Fri, 03 Apr 2020 10:28:06 GMT

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 )