Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Partner
Partner

How to accumulate in a Pivot table?

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.

5 Replies

Re: How to accumulate in a Pivot table?

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

jayanttibhe
Contributor III

Re: How to accumulate in a Pivot table?

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/Inte...

Partner
Partner

Re: How to accumulate in a Pivot table?

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

jayanttibhe
Contributor III

Re: How to accumulate in a Pivot table?

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?

You can do what jayanttibhe‌ mentioned, or use this

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