Loop through dates

[#1]

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

[hic]

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

[#3]

Becareful about leap year (like this year) which will put the dates off again.

Stephen

[#4]

Okay so close. But I'm comparing 1/5/2012 to 1/4/2011.

[#5]

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

[#6]

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