Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

14 Replies
Not applicable
Author

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
Former Employee
Former Employee

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

Not applicable
Author

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
Former Employee
Former Employee

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

Not applicable
Author

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
Former Employee
Former Employee

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

Not applicable
Author

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
Former Employee
Former Employee

Is this roughly what you want?

Pivot.png

Not applicable
Author

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.