Cumulative sum of data in pivot Table column wise

[Anonymous]

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

[sunny_talwar]

May be try this:

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

[marcus_sommer]

Try the following:

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

- Marcus

[Anonymous]

Thanks for the reply Sunny,

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

[Anonymous]

Thanks Marcus, this one is working fine..

[sunny_talwar]

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