Leap year daily comparison with day as dimension

[chrisevans_cj]

Good day,

I need to compare on a daily basis where the dimension is day. The comparison is for previous year vs current year - and you can see the pattern is a day behind for the previous year on the 4, 11, 18, 25. i know the day dimension would be the main issue, but the requirement is to see a daily comparison for a month on the previous year, and the leap year is one of the contributing factors. The weekly, monthly and annual ones are fine it's only this view (and we want the day as a dimension, even though it is not possible - do i create another field in master calendar to have date-1 for previous year - but then the leap year will cause an issue).  Any suggestions?

MasterCalendar: LOAD
DATE(DATE) AS DATE
,DAY(DATE) AS DAY, DAYNUMBEROFYEAR(DATE) AS DAYOFYEAR, WEEKDAY(DATE) AS WEEKDAY
,WEEK(DATE) AS WEEK, WEEKSTART(DATE) AS WEEKSTART, WEEKEND(DATE) AS WEEKEND
,MONTH(DATE) AS MONTH, MONTHSTART(DATE) AS MONTHSTART, MONTHEND(DATE) AS MONTHEND
,YEAR(DATE) AS YEAR, DAY(DATE) & ' ' & MONTH(DATE) AS DAYMONTH, MONTH(DATE) & ' ' & YEAR(DATE) AS MONTHYEAR
;LOAD DATE(MIN + ITERNO()) AS DATE, MAX WHILE MIN + ITERNO() <= MAX; LOAD MIN(FIELDVALUE('DATE', RECNO()))-1 AS MIN, MAX(FIELDVALUE('DATE', RECNO())) AS MAX AUTOGENERATE FIELDVALUECOUNT('DATE');

[sunny_talwar]

I guess before we propose anything, what exactly is the business requirement for comparing 29th Feb to? Is it compared to 28th Feb or does not get compared to anything?

[chrisevans_cj]

Hi Sunny,

Essentially we are saying this year on the 4th it was a Sunday, the year before - same Day and Month was a Saturday. The business wants the dimension to remain 1-31 but have last year reflecting on the week and day - if that makes sense.

[sunny_talwar]

It does make sense, but I am still not sure what will be the last year for Feb 29th 2020?