Cumulative Sum - Sorting AGGR(RANGESUM(ABOVE()))

kmstephenson — Mon, 05 Nov 2018 21:27:06 GMT

Hi All,

I have the following expression that is not giving me the desired "cumulative" payment results for YTD:

Aggr(RangeSum(Above(Sum([PaymentAmount]),0,RowNo())),[Month/Year])

The Month/Year field includes Jan-18 through Sep-18. It shows January payments as higher than February payments even though this should never be the case because it's cumulative (it should be an increase every month) - I'm guessing it has to do with the sorting and using the Above function? I.e. there is no row on the table for months with 0 payments.

How do I ensure that the cumulative starts at Jan-18 if not all patients have a Jan-18 payment? There is always at least one patient with a Jan-18 payment though.

Thanks!

Re: Cumulative Sum - Sorting AGGR(RANGESUM(ABOVE()))

sunny_talwar — Tue, 06 Nov 2018 01:09:43 GMT

Are you using QV12 or above? If you are... then try this (assuming that your Month Year field is a dual field which has an underlying numeric value)

Aggr(RangeSum(Above(Sum([PaymentAmount]),0,RowNo())), ([Month/Year], (NUMERIC)))

Read about this here

The sortable Aggr function is finally here!

topic Re: Cumulative Sum - Sorting AGGR(RANGESUM(ABOVE())) in QlikView

Cumulative Sum - Sorting AGGR(RANGESUM(ABOVE()))

Re: Cumulative Sum - Sorting AGGR(RANGESUM(ABOVE()))