Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Pivot table---cummulative--rangesum

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.

1 Solution

Accepted Solutions
Highlighted

Try this expression:

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

Also attaching your application for reference. (PFA)

Best,

Sunny

View solution in original post

4 Replies
Highlighted

Try this expression:

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

Also attaching your application for reference. (PFA)

Best,

Sunny

View solution in original post

Highlighted
Creator
Creator

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

Highlighted

‌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

Highlighted
Contributor
Contributor

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 )