Why does the calendar date field and the date field of another table sometimes not match up in QlikView?

Why does the calendar date field and the date field of some other table some times not match up in QlikView?

Ok, so I have two tables, one a Calendar table and one a table of of support cases (pulled from Salesforce). They both have a column called CalendarDate and both have the same date values. The table for support cases looks something like this (obviously this is a minimal example).

CaseNumber, CalendarDate
123456, 1/1/2010
123457, 1/2/2010
123458, 1/2/2010
123459, 1/2/2010
123460, 1/2/2010
123461, 1/3/2010
123462, 1/3/2010
123463, 1/3/2010

The Date value is calculated from a field and converted using the Date() function: Date(sourcecolumn)

The Calendar table of course has days and months, etc…For more information about my calendar, go here:
http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/

Anyway, I should be able to create a chart that has CalendarDate as the Dimension and Count(CaseNumber) as the Expression and get something like the following:

However, it wouldn’t work. This is too simple to possibly fail, right? Is it a QlikView bug? Or a bug with the Salesforce plugin?

What is interesting, is that if I do a select field, which only shows unique values, all the values show up:

1/1/2010
1/1/2010
1/2/2010
1/2/2010
1/2/2010
1/2/2010
1/2/2010
1/3/2010
1/3/2010
1/3/2010
1/3/2010

Now that is just not right. They are the same value right? Well, obviously not if only unique values show.

So how could they be different?

Well, QlikView doesn’t actually store the date, it stores a number. So obviously the number is different somehow. Maybe it is because my Calendar creates the CalendarDate using an integer but the Salesforce date values come in as doubles.

So here is why I think this is a Salesforce bug. I am calling Date() against the value and it is creating the 1/1/2010.

I have to say that I feel that QlikView needs to normalize this data, so that a Date created using Date(Integer) that returns 1/1/2010 and a Date created using Date(Double) that returns 1/1/2010 should match.

I did find a simple workaround. If I do this to round the double to an integer, it works.

Date(Round(DateAsDoubleValue - .5, 1)) as CalendarDate,

I am not sure if this is by QlikView design, but personally, I would prefer that If I call Date() on any data type, the return values should be normalized and match.

Leave a Reply

How to post code in comments?