Leap year issue

[CodingNini]

I am looking to retrieve all the days of the previous year within my date range.
However, with this formula, for leap years, it does not take into account February 29th.

The variable DateCloture is filled with a value such as 28/02/2025.

And I want to retrieve the measure for the period from 01/02/2024 to 29/02/2024.

How should I proceed, please?

SUM({1<TYPE={"F*"},
DATE={">=$(=NUM(MonthStart(ADDMONTHS($(vDateCloture),-12))))<=$(=NUM(MonthEnd(ADDMONTHS($(vDateCloture),-12))))"},
DOMAINE={"*P"}>} TOT*TAUX)/1000

[Or]

February 29th is not within the range of February first and February 28th...?

That said, you could just reverse the MonthEnd and AddMonths, presumably. Right now you're calculating the month end first, then going back one year. If you go back one year and then get the month end from that, it should include the 29th if relevant.

[marcus_sommer]

You may try to skip addmonths() completely because monthstart/monthend() have itself a second parameter to define the target-period.

Further, all the above functions are returning a timestamp and not a date and num() didn't change this. Better would be to wrap it with floor().

Beside this could the above approach be simplified with a continuous period-field, manually built like: year * 12 + month or created with interrecord-functions or an autonumber() within the calendar.

[Vegar]

@CodingNini  I believe you are on the correct path.

I am suspecting you have some issues with your $(vDateCloture) variable that gets intepreted as 28/2/2025=0,0069

Try this expression. (You can omit the date#() part if you already have the date format in your app set as DD/MM/YYYY)

SUM({1<
TYPE={"F*"},
DATE={">=$(=(MonthStart(ADDMONTHS(date#('$(vDateCloture)','DD/MM/YYYY'),-12))))<$(=(MonthEnd(ADDMONTHS(date#('$(vDateCloture)','DD/MM/YYYY'),-12))))"},
DOMAINE={"*P"}
>} TOT*TAUX)

In my screenshot TOT*TAUX = 1 you see the full 29 days output when vDateCloture=28/02/2025

[CodingNini]

Sorry, it still doesn't work and returns 0 instead of 991

My variable vDateCloture returns 45716

[CodingNini]

I'm sorry, but the result is not as expected.

If I remove NUM and AddMonths, I get a number that has nothing to do with the expected result (25888 instead of 991).

[CodingNini]

I'm sorry, but the result is not as expected.
When I swap the values of MontEnd and AddMonths, I get the same result.

[Or]

=MonthEnd(AddMonths(MakeDate(2025,2,28),-12))

Returns February 29th 2024 11:59:59 pm. Checked and confirmed on my instance. Check the rest of your code and data...

[marcus_sommer]

You shouldn't try to resolve your issue within the origin expression else separating each single part as an own expression - starting with the most inner part which is the variable, then the addmonths(), then the monthstart() and so on. It's quickly done and will show which one didn't returned the needed intermediate result.

[CodingNini]

It's all good, I found how to make it work

Actually, the problem was with the start date, not the 29/2 as I initially thought.

SUM({1<TYPE={"F*"}, DATE={">=$(=NUM(YearStart($(vDateCloture), -1))) <= $(=NUM(MonthEnd(ADDMONTHS($(vDateCloture), -12))))"}, DOMAINE={"*P"}>} TOT*TAUX)/1000