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

[#2]

It appears that your vMinDate_Num_Adjusted calculation always* gives you the date 364 days ago. Why not use

LET vMinDate_Num_Adjusted = Date(vMaxDate - 364);

instead?

* Avoid any leap year determination in your scripts since leap years are not always every fourth year. http://en.wikipedia.org/wiki/Leap_year#Algorithm

[hic]

All QlikView functions use the correct Gregorian leap year algorithm. So, just as vhuynh says - avoid any leap year calculation. You do not need any.

If you want to set the date back one year, use the AddYears function:

Let vMinDate_Num_Adjusted = AddYears(vMaxDate, -1);

If you want to compare this year's day n with last years day n, then last year's day n is found through:

Let vMinDate_Num_Adjusted = vMaxDate - MakeDate(Year(vMaxDate)) + MakeDate(Year(vMaxDate)-1) ;

HIC

[#4]

Okay, I didn't know about the gregorian leap year algorithm. So that helps, but your calculation still doesn't come up with the right day. For example:

Saturday 3/31/2012 should be compared to Saturday 4/2/2011. But your calculation says 4/1/2011.

Also, I want to do this calculation over a period of time. Say take each day and minus it by its previous day to gets its delta. This variable only allows me to grab the maxdate. What am I missing?

Alex

[hic]

Since you want to do this on table data, you should define your formulas inside a Load statement. For instance, your date calculation should be

   Date(Date - YearStart(Date) + MakeDate(Year(Date)-1) +1) as OneYearAgo

Further, if you want to compare one record with the previous to calculate a diff, then you could use the peek() function in the script, e.g.

   Amount-peek(Amount) as Diff

or you can use the above() function in a chart, e.g.

   Sum(Amount) - Above(Sum(Amount))

HIC

[#6]

Okay, i will attempt to build that in. So this is my expression. I'm getting the correct numbers for the LHS. But when I try to compare against last years day, it is comparing the same date, not day. I've attached the view of what my client is attempting to do. Can I change the As of Date by 1 or 2, depending on leap year?

=sum({$<"Date Type"={'Rolling 28 Days'},"As of Date"={">=$(=addmonths(max("As of Date")+1,-12)) <=$(=max("As of Date"))"}>}Cnt)-

sum({$<"Date Type"={'Rolling 28 Days'},"As of Date"={">=$(=addmonths(max("As of Date")+1,-24)) <=$(=addmonths(max("As of Date"),-12))"}>}Cnt)

[hic]

Yes, you can add or subtract any way you want.

But the formula above for "OneYearAgo" already compensates for leap years. It calculates the day number of the year using "Date - YearStart(Date)" and then adds the YearStart of previous year.

HIC

[#8]

Right, but how do I find the difference between the two?

Could you build me a quick sample? I've attached my application that I'm working in if it helps at all.

[hic]

Is this roughly what you want?

[#10]

Not sure, its hard to distinguish what the Date of the DayNumberofYears is. My gut feeling says no since, DayNumberofYear for 1/6/2011 should be 6, not 5.

Remember I'm just trying to compare 1/6/2011 to 1/5/2012.