Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ishika00730
Contributor III
Contributor III

Accumulation in a Pivot Table

Hi ,

I am trying to create a pivot table where I have to show the Year (dimension) and Count of Customers (Measure/Exp). I have shown this is a bar chart and accumulated the count of customers over years. The approach is very simple in a bar chart. Just click on the accumulation and it works. But the user wants to see the same information in a pivot table.

Now when I calculate the same in the pivot, there is no option to perform an accumulation. Can someone suggest me how to do accumulation in a pivot table.

The requirement is a bit urgent. Any help is highly appreciated.

Thanks

Ishika

1 Solution

Accepted Solutions
alexandrakfcosta
Contributor III
Contributor III

Hi,

Like Neelam mentioned, you have t use the function RangeSum combined with Before or Above.

If you want to do accumulation in the columns you should use

RangeSum(Before(Count(Customer), 0, ColumnNo()))

If you do accumulation in the rows, you should use

RangeSum(Above(Count(Customer), 0, RowNo()))

The use of TOTAL depends if you want to sum all the values or just the ones that are displayed in the pivot table.

The use of the number 0 (zero) as the second parameter depends if you want to include the current row/column, if not, you can use the number 1 (one).

I hope I could help.

Best,

Alexandra.

View solution in original post

4 Replies
neelamsaroha157
Specialist II
Specialist II

Try this -

RangeSum(Above(TOTAL Count(Customer), 0, Rowno(TOTAL)))

alexandrakfcosta
Contributor III
Contributor III

Hi,

Like Neelam mentioned, you have t use the function RangeSum combined with Before or Above.

If you want to do accumulation in the columns you should use

RangeSum(Before(Count(Customer), 0, ColumnNo()))

If you do accumulation in the rows, you should use

RangeSum(Above(Count(Customer), 0, RowNo()))

The use of TOTAL depends if you want to sum all the values or just the ones that are displayed in the pivot table.

The use of the number 0 (zero) as the second parameter depends if you want to include the current row/column, if not, you can use the number 1 (one).

I hope I could help.

Best,

Alexandra.

ishika00730
Contributor III
Contributor III
Author

Hi Alexandra & Neelam,

You guys are awesome.  One more help needed.

Since I have the expression by Customer Join Month, the last month has the maximum value. I want to sort this table now in desc order. But when I sort it in desc order, the 1st month starts showing the maximum value after sort. I know that this is because of the expression.

Please suggest some solution .

Thanks

Ishika

ishika00730
Contributor III
Contributor III
Author

Thanks a lot Neelam.