topic Re: How to accumulate in a Pivot table? in QlikView

How to accumulate in a Pivot table?

neena123 — Tue, 09 Oct 2018 16:44:47 GMT

I want to show the accumulate by month per year in a pivot table. Is it possible? How would I accomplish this? I attached a photo of what my pivot table looks like. Basically a accumulation month over month.

Re: How to accumulate in a Pivot table?

sunny_talwar — Tue, 09 Oct 2018 16:52:46 GMT

So you want to accumulate across your months and not go down? Use ColumnNo() instead of RowNo() and Before/Atfer instead of Above/Below in your RangeSum() function

Re: How to accumulate in a Pivot table?

jayanttibhe — Tue, 09 Oct 2018 17:01:07 GMT

Hi -

You can use

For Counts

Rangesum(before(Count[Field]),0,12))

For Sum

Rangesum(before(Sum[Field]),0,12))

where - 0 is an offset and 12 is the "Counter" - assuming you want to accumulate it for 12 months. You may adjust it based on your need.

FYI

https://help.qlik.com/en-US/sense/September2018/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterRecordFunctions/bef…

Re: How to accumulate in a Pivot table?

neena123 — Tue, 09 Oct 2018 17:45:07 GMT

Yes I want it to accumulate across. So would I have 2 expressions? Currently I have SUM(Net Sales Amount).

Re: How to accumulate in a Pivot table?

jayanttibhe — Tue, 09 Oct 2018 18:05:34 GMT

Ok then use

Rangesum(before(Sum[Net Sales Amount]),0,12))

I have just given examples of Sum and Count. You can use as per your need.

Re: How to accumulate in a Pivot table?

sunny_talwar — Tue, 09 Oct 2018 18:10:28 GMT

You can do what jayanttibhe‌ mentioned, or use this

RangeSum(Before(Sum([Net Sales Amount]), 0, ColumnNo()))