Last day of X months ago's income

[kbenzakri-lelp]

Hello, I'm trying to run a formula to display the income of a certain day in the previous year, but it doesn't work, here is the formula : 

[DATA DSO.Date cpt..] = Date Column

[DATA DSO.Mtant en DI] = Income Column

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.

[vincent_ardiet_]

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

[kbenzakri-lelp]

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

[vincent_ardiet_]

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

[kbenzakri-lelp]

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.