Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
RangeSum(Above(TOTAL Sum({<Date={">=$(=AddMonths(MonthEnd(Max([DATA DSO.Date cpt..])), -12))<=$(=MonthEnd(Max([DATA DSO.Date cpt..])))"}>} [DATA DSO.Mtant en DI]), 0, 12))
As an example in the bottom of the page, Qlik gives :
RangeSum(Above(TOTAL Sum({<Date={">=31/07/2022<=31/07/2023"}>} [DATA DSO.Mtant en DI]), 0, 12))
Which is exactly the dates I want.
However, when I show this Measure in a pivot table with [DATA DSO.Date cpt...autoCalendar.YearMonth] as Column, it only shows the sum for the 12 last months, not including the last day of n-13 month.
I've tried a different approch on number of days, but it didn't end as I expected. the base formula I used looked like this :
RangeSum(Above(TOTAL Sum({<Date={">=$(=Date(MonthEnd(Max([DATA DSO.Date cpt..])) -365,'DD-MM-YYYY'))<=$(=Date(MonthEnd(Max([DATA DSO.Date cpt..])), 'DD-MM-YYYY'))"}>} [DATA DSO.Mtant en DI]), 0, ?????))
I've tried to replace ????? by RowNo(), 365... Nothing worked.
I've tried something simple, to show only d-365 income like this :
Sum({<Date={"=$(=Date(AddYears(MonthEnd(Max([DATA DSO.Date cpt..])), -1),'DD-MM-YYYY'))"}>} [DATA DSO.Mtant en DI])
Did not work as well.
If you need me to export the App, I'll need to rework the data as contains sensitive data, let me know if you need that.
Maybe like this:
Sum({<Date={">=$(=AddMonths(MonthEnd(Max([DATA DSO.Date cpt..])), -12))<=$(=MonthEnd(Max([DATA DSO.Date cpt..])))"}>}
Aggr(RangeSum(Above(TOTAL Sum([DATA DSO.Mtant en DI]), 0, 12)), ([DATA DSO.Date cpt...autoCalendar.YearMonth],(NUMERIC,ASCENDING)) ))
Thanks for your help, but I'm getting the exact same result as my formula(s) gave.
Just to re-confirm, I've checked the data many times, the missing delta indeed comes from that particular day that Qlik does not take into account (the one day that is 365 days before).
Ah ok, I think I see, this could be caused by the fact that MonthEnd is also setting the time to 23:59.
Sum({<Date={">=$(=AddMonths(Floor(MonthEnd(Max([DATA DSO.Date cpt..]))), -12))<=$(=MonthEnd(Max([DATA DSO.Date cpt..])))"}>}
Aggr(RangeSum(Above(TOTAL Sum([DATA DSO.Mtant en DI]), 0, 12)), ([DATA DSO.Date cpt...autoCalendar.YearMonth],(NUMERIC,ASCENDING)) ))
Still the same result. I've also tried my formula with an extra - 1 to take even more days into account, but still the same result too.