topic Cumulative sum of data in pivot Table column wise in QlikView

Cumulative sum of data in pivot Table column wise

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

Hi everyone,

The requirement is that I have to show cumulative sum of data column wise in a pivot table. The Expression I'm using is

RangeSum( Before(Sum( PatientCount),0,ColumnNo()))

For reference I'm showing the table output. The dimensions taken in table are year, MonthOfYear, patientCategory

\In that case I'm facing two chalanges.

1. The expression is doing the cumulative sum for one year, when a new year starts, the first data is not the cumulative sum data, rather it is the real data only. Just like in table, cumulative sum upto 15-12 is fine, but when 16-01 starts, it is not the cumulative data.

2. The range coming up to 17-04. But I have data up to 17-02 in my data table.

It will be highly appreciable if someone give me some possible solution for these problems..

Re: Cumulative sum of data in pivot Table column wise

sunny_talwar — Tue, 21 Mar 2017 08:32:18 GMT

May be try this:

If(Sum(PatientCount) > 0, RangeSum(Before(TOTAL Sum(PatientCount), 0, ColumnNo(TOTAL))))

Re: Cumulative sum of data in pivot Table column wise

marcus_sommer — Tue, 21 Mar 2017 08:37:41 GMT

Try the following:

RangeSum( Before(Sum({< PatientCount = {'*'}>} TOTAL <Patient_Monitoring> PatientCount),0,ColumnNo()))

- Marcus

Re: Cumulative sum of data in pivot Table column wise

Anonymous — Tue, 21 Mar 2017 10:43:59 GMT

Thanks for the reply Sunny,

Concern is that "if" logic affects performance. So I'm trying to avoid it unless absolutely necessary...

Re: Cumulative sum of data in pivot Table column wise

Anonymous — Tue, 21 Mar 2017 10:44:48 GMT

Thanks Marcus, this one is working fine..

Re: Cumulative sum of data in pivot Table column wise

sunny_talwar — Tue, 21 Mar 2017 10:55:18 GMT

Please close the thread by marking Marcus's response if you got what you were looking for.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny