Hello – Shawn has asked me to help out with this blog from time to time, which I’m more than happy to do. I, however, lean a little more toward the more technical aspects of QlikView, so that’ll be my focus on this, and subsequent posts to QlikThoughts. (That’s my “WARNING: Geek speak coming up”.)
Yesterday, in QlikView, I made a seemingly common mistake of linking up a standard fact table (it was a table of marketing responses) to a calendar dimension table. This is a pretty basic task in QlikView, but for some reason the Date() formatting function is an easy one to get wrong.
Here are the details:
In the fact table, I have a date… let’s call this MyDate. To make a link between the table and the calendar I did the following, and put the same DATE_KEY field in the calendar.
Date(MyDate) AS DATE_KEY
The problem is that the Date() function doesn’t simply return what I imagine a date to be, it returns a numerical representation of a date, which can be fractional. I want to be clear here: this is stated in the documentation – I just have to remember it. Using the code above yields the following results (both are the same field in a list box, just formatted differently).
That looks like a problem - I wanted to build a date dimension, I didn’t want a date/time dimension (which most likely should be two different dimensions, but that’s another topic). One quick tipoff to this problem is to look at the Tables tab under Document Properties and make sure the number of rows in your calendar table corresponds to the number of days in your data (and isn’t, for example, fifty times as big).
I fixed this problem with “rounding” the number to the start of the day before I put it in the date function by using the DayStart() function.
Date(DayStart(MyDate)) AS DATE_KEY
I’ve seen other people post that they use something like:
Date(Floor(MyDate)) as DATE_KEY
And that rounds off in the same way. Both give you:
I did try explicitly setting the date format by using Date(MyDate, 'MM-DD-YYYY'), but that did not get rid of the decimal portion either. So, as you continue working with your own QlikView applications, make sure you keep in mind the nuances of the MyDate function and try to avoid one of those simple little mistakes that is easy to overlook.
Until next time…Paul.