Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Okay, so I've created these variables based on max date. How would I change the vShift variable to loop through all of my dates? I want to set my dates back for leap years and compare this years day, to last years day.
LET vMaxDate1 = Date('$(vMaxDate)');
LET vMaxDate_Num = num('$(vMaxDate)');
LET vDayOfWeek_Max = weekday('$(vMaxDate)');
LET vDayOfWeek_Max_Num = num(weekday('$(vMaxDate)'));
LET vShift =
IF (
(mod(year('$(vMaxDate)'), 4) = 0
AND
($(vMaxDate_Num) >= num(makedate(year('$(vMaxDate)'), 2, 29))))
OR
(mod(year('$(vMaxDate)'), 4) = 1
AND
($(vMaxDate_Num) < num(makedate(year('$(vMaxDate)'), 3, 1))))
, 2, 1);
LET vMinDate = addmonths('$(vMaxDate)', -12, -1);
LET vMinDate_Num = num('$(vMinDate)');
LET vDayOfWeek_Min = weekday('$(vMinDate)');
LET vDayOfWeek_Min_Num = num(weekday('$(vMinDate)'));
LET vMinDate_Num_Adjusted = $(vMinDate_Num) + $(vShift);
LET vMinDate_Adjusted = date('$(vMinDate_Num_Adjusted)', 'MM/DD/YYYY');
LET vDayOfWeek_Min_Adjusted = weekday('$(vMinDate_Adjusted)');
LET vDayOfWeek_Min_Num_Adjusted = num(weekday('$(vMinDate_Adjusted)'));
All you need to do then is to create your own field DayNumberOfYear. Don't use the function. Instead, you can use, e.g.
Date - YearStart(Date) + 1 + Year(Today()) - Year(Date) as DayNumberOfYear
The two last terms will shift the dayNo by 1 for last year, two for the year before that etc. Then you will compare 1/6/2011 with 1/5/2012.
HIC
Becareful about leap year (like this year) which will put the dates off again.
Stephen
Okay so close. But I'm comparing 1/5/2012 to 1/4/2011.
Did you ever get this to work? I tried using both these, and it doesn't seem to be accounting for leap year. For 1/3/2009 (Sat) I am getting 1/4/2008 (Fri) instead of 1/5/2008 (Sat) for both of these.
AddYears(Datefield, -1) as same_day_last_year1
Date(Datefield - YearStart(Datefield) + MakeDate(Year(Datefield)-1) +1) as same_day_last_year2
Actually, I think I just figured it out. You can use this in your load and it works. Don't ask me what it is doing or tell me how ugly it is, because I pieced together about 5 different answers to get here, so I'm going with it 🙂
if
(mod(Year(Datefield)-1,4) = 0 and mod(Year(Datefield)-1,100) <> 0 or mod(Year(Datefield)-1,400) = 0, Date(Datefield - YearStart(Datefield) + MakeDate(Year(Datefield)-1) +2), Date(Datefield - YearStart(Datefield) + MakeDate(Year(Datefield)-1) +1)) as same_day_ly