14 Replies Latest reply: Aug 29, 2012 4:52 PM by Christine Hill

# Loop through dates

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

• ###### Loop through dates

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

* 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

• ###### Loop through dates

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:

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

• ###### Loop through dates

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

• ###### Loop through dates

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

• ###### Re: Loop through dates

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

• ###### Re: Loop through dates

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

• ###### Re: Loop through dates

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.

• ###### Re: Loop through dates

Is this roughly what you want?

• ###### Re: Loop through dates

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.

• ###### Re: Loop through dates

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

• ###### Loop through dates

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

Stephen

• ###### Re: Loop through dates

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

• ###### Re: Loop through dates

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.

Date(Datefield - YearStart(Datefield) + MakeDate(Year(Datefield)-1) +1) as same_day_last_year2

• ###### Re: Loop through dates

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