Rhyous

January 14, 2010

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

Filed under: QlikView — J. Abram barneck @ 9:25 pm

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.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: