Date comparison issue

[LeanMestra]

Hi everyone!

I am facing a problem related to date comparisons between years. I need to compare a one-year date with the "corresponding" date of the last year (ie Friday Jan 1, 2021 vs. Friday Jan 3, 2020, or Friday Dec 31, 2021 vs. Friday Jan 1, 2021).

Everything worked fine until the turn of this year 2022 as I am not getting the desired values:

As seen in the image above, the red dates are "wrong" and the "desired dates" are in the green square.

I am currently using this expression and it has been working correctly so far:

=MakeWeekDate( year(FechaCalendar)-1,
week(makedate(year(FechaCalendar),month(FechaCalendar),day(FechaCalendar)),0,1,1),
weekday( makedate(year(FechaCalendar),month(FechaCalendar),day(FechaCalendar)),0)
)

*FechaCalendar is my date field

I think it must be related to the "first day of the week" or the "number of weeks".

Any help or advice?

Thanks in advance.

#weekdate #year #yoy #qliksense

[MarcoWedel]

How is this corresponding date defined?

[LeanMestra]

It is a business definition,  mainly based on the day of the week, for example, the first Friday of the first month of the year versus the first Friday of the first month of the previous year.