Cumulative Sum in Qlikview

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

Hi All,

I have a requirement to show Cumulative sum in a Qlikview Pivot Table. I have created a dimension table required for the Report and assigned proper keys to the rows that need cumulative sum.

The formula for the Cumulative sum works fine,but if there is a gap in the data e.g. for certain dimension there is no data then the Cumulative cell is blank.Attached sample table for Expected and Actual result.

Formula I use is as below :

=IF(MATCH(D1_KEY,'C1','C2','C3'),RANGESUM(BEFORE(TOTAL SUM(Amount),0,ColumnNo(TOTAL))),SUM(Amount))

I know that one solution is to have at least dummy 0 value for amount for all dimensions,but my data is huge and there can be many permutations and combinations so very difficult to populate 0 for all dimensions.

Please suggest if there is any way to tweak the Set Analysis to get the desired result.

Expected and Actual result as attached,highlighted in Orange are the cells not getting populated but i need the result same as in the Expected result Table.

Thank you.

topic Cumulative Sum in Qlikview in QlikView

Cumulative Sum in Qlikview