topic Cumulative Formula Previous Month in QlikView

Cumulative Formula Previous Month

Chloe19 — Wed, 28 Jul 2021 00:07:32 GMT

I have the below formula to give me the cumulative.

However, how would I amend it to give me the cumulative for the previous month or 2 months ago.

So regardless is I select to filter on MAY it would give me APRIL or if I filter JUN it would give me MAY

$(accum)Sum(TOTAL_CASH)$(/accum)

Thank you

(see attached what I achieved in POWER BI but I am struggling replicating in QLIK (below is the POWER BI formula)

Cummulative Cash = CALCULATE([Total Cash],FILTER(ALLSELECTED('Date'),'Date'[Date] <= MAX('Date'[Date])))

Re: Cumulative Formula Previous Month

stevejoyce — Wed, 28 Jul 2021 00:13:48 GMT

If the date/time dimension is used in your chart, you can use rangesum(above()) which you can pass through your expression, start (t-1, t-2, etc) and end row (current row value).

https://community.qlik.com/t5/QlikView-App-Dev/Rangesum-Above-Below-and-Before-After-and-Range-Avg/m-p/533278#M1124022

Re: Cumulative Formula Previous Month

Chloe19 — Wed, 28 Jul 2021 00:59:52 GMT

Thanks for the response I tried the below, doesn't work, no result, any suggestions?

[Cash Date] is my date dimension

aggr(RANGESUM(ABOVE($(accum)Sum(TOTAL_CASH)$(/accum), 0, ROWNO()) ), [Cash Date], (-1))