topic Accumulation in a Pivot Table in QlikView

Accumulation in a Pivot Table

ishika00730 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Accumulation in a Pivot Table

neelamsaroha157 — Thu, 30 Aug 2018 14:10:35 GMT

Try this -

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

Re: Accumulation in a Pivot Table

alexandrakfcosta — Thu, 30 Aug 2018 14:25:49 GMT

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.

Re: Accumulation in a Pivot Table

ishika00730 — Fri, 31 Aug 2018 01:44:50 GMT

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

Re: Accumulation in a Pivot Table

ishika00730 — Fri, 31 Aug 2018 01:45:24 GMT

Thanks a lot Neelam.