Qlik Community

Qlik Design Blog

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

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

HIC

22 Comments
Partner
Partner

It is very good blog to understand on date functionality.

3,667 Views

Concise and well explained. Thanks!

0 Likes
3,667 Views
manideep78
Valued Contributor

After reading this I understood the  Date functionality correctly.

Nice Blog.

0 Likes
3,667 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
3,667 Views

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
3,667 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
3,667 Views
thanstad
Contributor

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
3,667 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
3,667 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
3,667 Views

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

3,667 Views
thanstad
Contributor

Thanks

0 Likes
3,667 Views
robert99
Valued Contributor II

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

0 Likes
3,667 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
3,667 Views

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
3,667 Views
Partner
Partner

Thank you HIC, Very well explanation 🙂

0 Likes
3,667 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
3,667 Views
vireshkolagimat
Contributor III

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
3,667 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
3,667 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
3,667 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

3,667 Views
kaustubhgarg
New Contributor

Thanks Steen

It gets resolved using aggr function in calculated dimension.

Regards

Kaustubh

0 Likes
3,667 Views
vadim_grab
Contributor

Hi, Henric

Can you help me?

Problems with loading the Dates

Many thanks in advance!

Regards

Vadim

0 Likes
3,667 Views