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

[kmstephenson]

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!

[sunny_talwar · Accepted solution]

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!