Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Why don’t my dates work?

A common recurring question on the QlikCommunity forum is around dates that don’t work. Here follows a help on fixing the three most common causes. If you encounter such a question on the forum, just link to this post in your answer.


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)

ListBoxNumberFormatting.png

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.

DateCheck.png

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)

ListBoxTimeStampFormatting.png

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.

TimeStampCheck.png

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 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.

HIC

22 Comments
jagannalla
Valued Contributor III

It is very good blog to understand on date functionality.

515 Views
julian_rodrigue
Valued Contributor

Concise and well explained. Thanks!

0 Likes
515 Views
manideep78
Valued Contributor

After reading this I understood the  Date functionality correctly.

Nice Blog.

0 Likes
515 Views
Not applicable

Hi Henric,

Its another one of your great entries on the nuances of qlikview! Thank you for the clarifications.

However I face a slightly different problem. I have a date string like 'Mon, 06/01/2014 20:10:35 GMT'. How do I construct the format string in the date#/timestamp# function in order to read in such a format??

I tried: 'WWW, DD/MM/YYYY hh:mm:ss GMT' but it did not work. In particular I am unable to interpret the weekday using Date#/Timestamp#. What am I doing wrong here? Or am I supposed to use a different function altogether?

Thank you for your help in advance!

Thyagesh

0 Likes
515 Views
Employee
Employee

There are two problems with your format string. The first is the weekday and the 'WWW' code. For some reason this doesn't work and I suspect that the reason is that you can only use the 'WWW' in formatting functions and not in interpretation functions. But I need to check this with the developers to see how it is implemented to be sure.

The second is the 'GMT'. Time zones are not supported in the format code - it just has never been implemented. (I'll push for it. It ought to be there.)

So the work-around you need to use is;

  Mid(Date,26,3) as Timezone,

  Timestamp#(Mid(Date,6,19),'DD/MM/YYYY hh:mm:ss') as Timestamp,

If you don't want to hard-code the numbers in the Mid() functions, you can use the Index() function to find the proper position of the blankspace character. Finally, you can combine the Timestamp and the Timezone to create a new time, e.g.

  ConvertToLocalTime(Timestamp, Timezone) as LocalTime,

HIC

0 Likes
515 Views
john_novello
New Contributor II

This is awesome. Thanks! The Time/Stamp and Time formatting was like a light bulb going off for me.

0 Likes
515 Views
thanstad
New Contributor III

Question:

If I have two different tables with a correct datefield in each and i want one Mastercalendar to cover both. Can making a Linktable be a good solution ? Today I have two different Calendars covering each of them.

/Tormod

0 Likes
515 Views
Not applicable

Hi Henric,

Thank you for your quick reply! the solution works, but I used the subfield function to achieve the same effect.

It will be quite useful to standardise the format string elements or atleast let us specify wildcard '*' charaters so that arbitrary letters in between can be ignored. Thanks again for the solution!

Thyagesh

0 Likes
515 Views
Not applicable

Hi Tormod,

The solution depends on whether the date selection in one of the tables should affect the other. If we have a link table then it will affect and the effect is the same as simply renaming one of the fields to match the other. If we want the selections in each to be independent of the other then I guess we have to have a separate master calendar for each one.

Please correct me if I am wrong here.

Thyagesh

0 Likes
515 Views
Employee
Employee

I agree with Thyagesh.

Read more about using several calendars on http://community.qlik.com/blogs/qlikviewdesignblog/2012/08/30/master-table-with-multiple-roles

ore about using one common calendar for several dates on

Re: Master calendar without linking tables

HIC

515 Views
thanstad
New Contributor III

Thanks

0 Likes
515 Views
robert99
Valued Contributor II

Thanks Another problem that I have been working on. To exclude by date in SQL.

0 Likes
515 Views
Not applicable

Hi Henric,
I have a question regarding your post dated Jan 6, 2014 10:01 AM.

Curious whether you got around to checking with the developers about the interpretation functions.

I am attempting the following (no error produced), but the text is not converting to a dual so I assume it still isn't possible in the tool.

date#([Day of Week Name],'WWWW')

[Day of Week Name]=Monday, Tuesday, etc.

Thanks,

Mike

0 Likes
515 Views
Employee
Employee

The format code 'W' is only meaningful in formatting functions. It cannot be used in interpretation functions.

The reason is quite simple: The day of the week is redundant and can cause ambiguities. For instance

   'Sunday 10/31/2015'

contains an ambiguity: This date was a Saturday, and hence the string cannot easily be interpreted.

HIC

0 Likes
515 Views
psankepalli
Contributor III

Thank you HIC, Very well explanation :-)

0 Likes
515 Views
maxim_senin
Contributor III

Hi,

It seems one may want to know about difference between Date() and Date#(), please follow another Nenric's post for this - Get the Dates Right.

Best regards,

Maxim

0 Likes
515 Views
vireshkolagimat
Contributor II

Hi,

I have the below issue. Can you please help me on this.

I have attached data in my fact table and i am considering the transaction date to do incremental load.

Currently it captures the transactions from 12:00:00 AM to 11:59:59 PM . For example,If i want see the sales data for 11/29/2017 then it will be sum of all the transactions that happened  between 11/29/2017  12:00:00 AM to 11/29/201711:59:59 PM.


But i have a different business requirement where i need to consider the data from 8:00:00 AM to 8:00:00 AM.


In this case sales for 11/29/2017  would be the sum of transactions that occurred between 11/29/2017 8:00:00 AM to 11/30/2017  8:00:00 AM.


how can we achieve the same.


Thank you.


Regards,

Viresh

0 Likes
515 Views
maxim_senin
Contributor III

Hi Viresh,

If I correctly understood, in order to get the date you need it's just enough to check if Hour(DateTimeField) < 8 then Date(Date(DateTimeField)-1) else Date(DateTimeField)... as an idea, hope this helps.

Best regards,

Maxim

0 Likes
515 Views
kaustubhgarg
New Contributor

In script

if(sum(sales)<=5,dual('b1',5),

if(sum(sales)<=10,dual('b2',10),

dual('b3',20))) as bucket

Is there any problem in this syntax?

Please help.

Thanks and Regards

Kaustubh Garg

0 Likes
515 Views
sspe_dgs_com
Contributor II

It seems like you at least needs a Group By something clause at the end when you have a SUM. If you just want to put each record in the source into a "bucket" you can omit the SUM.

/Steen

515 Views
kaustubhgarg
New Contributor

Thanks Steen

It gets resolved using aggr function in calculated dimension.

Regards

Kaustubh

0 Likes
515 Views
vadim_grab
New Contributor III

Hi, Henric

Can you help me?

Problems with loading the Dates

Many thanks in advance!

Regards

Vadim

0 Likes
515 Views