topic Re: Loop through dates in QlikView

Loop through dates

Mon, 07 May 2012 19:06:53 GMT

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

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

Loop through dates

Tue, 08 May 2012 01:26:05 GMT

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

Loop through dates

hic — Tue, 08 May 2012 06:46:15 GMT

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

Loop through dates

Tue, 08 May 2012 13:09:03 GMT

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

hic — Tue, 08 May 2012 14:51:44 GMT

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

Thu, 10 May 2012 14:48:55 GMT

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)

Re: Loop through dates

hic — Thu, 10 May 2012 15:20:43 GMT

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

Thu, 10 May 2012 16:04:10 GMT

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

hic — Fri, 11 May 2012 09:31:44 GMT

Is this roughly what you want?

Re: Loop through dates

Tue, 15 May 2012 13:48:49 GMT

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

hic — Tue, 15 May 2012 21:55:36 GMT

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

Tue, 15 May 2012 22:01:17 GMT

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

Stephen

Re: Loop through dates

Wed, 16 May 2012 15:16:23 GMT

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

Re: Loop through dates

Wed, 29 Aug 2012 19:25:43 GMT

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

Re: Loop through dates

Wed, 29 Aug 2012 20:52:10 GMT

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 🙂

(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