Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
.png "Former Employee"){kind=icon}
1. Incorrect Date Interpretation
When data is loaded into QlikView, dates are often read as strings. QlikView then tries to recognize a pattern in the string that looks like the date format specified in the DateFormat environment variable. This sometimes fails and then you need to use the Date#() function to help QlikView understand that it is a date.
How do I know that a date is correctly interpreted? That’s easy. Just format it as a number and see what you get. (List box properties – Number – Integer)
The question is now what your list box looks like. If you have a number which is roughly 40000 (usually right-aligned), then you are all set. But if you still have a date stamp (usually left-aligned), then you need to use the Date#() function in the script. See QlikView Date fields.
2. Linking integer dates with fractional dates
You have a date in two different tables, and you want to use this date as a key, but it doesn’t seem to work. Then you should suspect that you have true dates (integers) in one table and timestamps (fractional numbers) in the other, but the formatting of the dates hides this fact.
How do I know whether this is the case? That’s easy. Just format it as a timestamp and see what you get. (List box properties – Number – TimeStamp)
The question is now what your list box looks like. If you have timestamps where hours, minutes and seconds are all zero, then you are all set. But if you have numbers in these places, then you need to use the Floor() function in the script to get integer dates. See QlikView Date fields.
3. Incorrect date comparisons
The most subtle error is however the one with timestamps in comparisons, e.g.
… Where Date = '2011-12-31';
Will this work? Yes, provided that the date format inside the string is recognized by QlikView, i.e. that it corresponds to the date format specified in the environment variable DateFormat in the beginning of the script.
It becomes even more complex if you use variables. Then it is important to use quotes correctly. The following will work:
Let vToday = Today();
… Where Date = '$(vToday)';
… but the following will not:
… Where Date = $(vToday);
The reason is that the $(vToday) will expand to a string containing the date, and then the comparison will be e.g.
… Where Date = 2/19/2013;
So the date (which is approximately 40000) will be compared to 2 divided by 19 divided by 2013, which of course is not what you want.
My recommendation is to always use numeric variables for dates. They always work - quotes or no quotes:
Let vToday = Num(Today());
… Where Date = $(vToday);
See more on QlikView Date fields.