article Dealing with date formats in QlikView and QlikSense in Member Articles

Dealing with date formats in QlikView and QlikSense

dominicmander — Tue, 14 Jun 2022 12:39:53 GMT

Date formats can often be challenging for beginners, particularly when different data sources supply dates in different formats, and your reporting requirements specify yet another final date format.

There are two main functions for dealing with date formats.

Date() and Date#()

Often I see people using the two functions in different combinations with a bit of trial and error until they get the result they want, without really understanding why it has worked. But, with a few simple basics understood, this is easy to understand.

First: Dates are a dual data type

Date fields, once properly formatted, are a dual data type. This means that each value in the field has two components, a string (text) representation of the date, and an underlying numeric component.

You may have seen when working with dates in Microsoft Excel that sometimes when you change the format of a date cell you end up with 42019 instead of 15/01/2015. This number is a difference, in days, between the date and an “epoch date”, in this case 01/01/1900. This is done, because this way it’s much easier to subtract one date from another to find the difference or add a number of days, months or years to a date to forecast for example.

Second: Each function manipulates only one component of that dual data type

Date#() takes a date string (e.g. the text ‘15/01/2015’) and calculates and adds the numeric component to make the proper dual data type date field. It leaves the string component unchanged.

Date() takes a number (either the number 42019 or an already properly interpreted date field with the underlying numeric component already in place) and calculates and adds (or changes) the string component. It leaves the numeric component unchanged.

Both functions take an optional second parameter as a format string for how to interpret in the case of Date() or how to format in the case of Date#(). If this second parameter is not provided, the default set in the script variables is assumed.

Example: A string in the format 2015-01-15 needs to be loaded, interpreted as a date, and formatted to 15/01/2015.

Let's assume the source field is called [OrderDate].

Solution: Date(Date#([OrderDate], 'YYYY-MM-DD'), 'DD/MM/YYYY') as Date

Reading from the inside out, we take the [OrderDate] field, apply Date#() to interpret the string and calculate the appropriate numerical component using the format mask YYYY-MM-DD, and then apply Date() to that result to reformat the string representation using the format mask DD/MM/YYYY.

Aside: Timestamps

Times and Timestamps take this simple concept one step further. In this case a time is again represented by a string (e.g. '12:00:00' or '15/01/2015 12:00:00') and the underlying numerical component will represent the time as a decimal fraction of a day (e.g. 0.5 or 42019.5). The corresponding functions are Time() and Time#() or Timestamp() and Timestamp#().

Re: Dealing with date formats in QlikView and QlikSense

rwunderlich — Mon, 25 Jan 2016 19:46:25 GMT

Excellent, concise explanation. One of the best explanations of Dates I've seen.

Re: Dealing with date formats in QlikView and QlikSense

dominicmander — Mon, 25 Jan 2016 21:38:30 GMT

Thanks Rob, appreciate it.

It's one of those things that I find self taught people who come to us for training have not often fully understood but is so simple once you explain it.

Re: Dealing with date formats in QlikView and QlikSense

Tue, 26 Jan 2016 22:33:12 GMT

Hello,

I am rather new to Sense and I'm in that trial and error mode you refer to.

I have been struggling with PRIOR YTD.

It would really help me if I knew what formats were allowed on the Date and Date# functions - is that stated anywhere in the documentation/help text? I see your examples use YYYY-MM-DD and DD/MM/YYY, are all combinations of Y M and D allowed such as YYMMDD?

Re: Dealing with date formats in QlikView and QlikSense

dominicmander — Wed, 27 Jan 2016 12:49:52 GMT

Good question ... assuming the 5th January 2015

YY or YYYY can be used for year e.g. 15 or 2015

M or MM or MMM or MMMM can be used for month e.g. 2 or 02 or Feb or February

D or DD can be used for day e.g. 5 or 05

W or WWW or WWWW can be used for weekday e.g. 4 or Thu or Thursday

Re: Dealing with date formats in QlikView and QlikSense

shanemichelon — Tue, 09 Feb 2016 06:00:16 GMT

I can never remember the format codes!

In Qliksense search the help for 'format codes'. The help topic is called 'Conventions for number and time formats'. It would be handy if it were referenced in the help topics for functions that use the format codes.

Re: Dealing with date formats in QlikView and QlikSense

sarahplymale — Fri, 11 Mar 2016 19:41:58 GMT

I have not seen such a simple explanation of the difference between date() - add/changes string component -and date#() - adds numerical component. Very helpful.

Re: Dealing with date formats in QlikView and QlikSense

Anil_Babu_Samineni — Wed, 03 Aug 2016 10:45:11 GMT

dominicmander,

Here, if i want to show date like a square b square like that, How to show that?

Like this,,

Let's Assume if date is 1-Aug-2016 Then i want to show like -- for that image, 1 is instead of a and st is instead of 2 -- 1st-Aug-2016("st" should be acting as square like image)

If 2nd Day -- nd

If 3rd Day -- rd

If 4th --- 30th Day -- th

If 31st Day -- st

Can i know how to achieve that? -- =Date(Now(),'DD' & '^rd' & ' ' & '-' & 'MMM-YYYY')

Re: Dealing with date formats in QlikView and QlikSense

dominicmander — Wed, 17 Aug 2016 10:48:31 GMT

I'm not sure that's possible I'm afraid ... at least I'm not aware of any way of doing that as a date format.

Re: Dealing with date formats in QlikView and QlikSense

Anonymous — Tue, 20 Dec 2016 12:58:55 GMT

I have some unwanted characters in my timestamp and 've been trying some different ways to format this into a date in QS, but have had no luck getting it to work. I'm hoping someone is smarter than me.

'October 11, 2016 at 08:37AM'

The 'at' and the AM/PM stuff is freaking me out and the source only publishes dates in this way. Can anyone help me figure out how to convert this string via the correct Input Format?

Re: Dealing with date formats in QlikView and QlikSense

rwunderlich — Tue, 20 Dec 2016 18:46:44 GMT

I think you'll have to parse it as two pieces and add them together:

=timestamp(

date#(SubField('October 11, 2016 at 08:37AM', ' at ', 1), 'MMMM D, YYYYY')

+ time#(SubField('October 11, 2016 at 08:37AM', ' at ', 2), 'hh:mmtt')

)

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Dealing with date formats in QlikView and QlikSense

Anonymous — Mon, 17 Jul 2017 08:46:29 GMT

This is really helpful, thanks a million.

Is the "epoch date" always set to '01/01/1900' in qlikview?

Re: Dealing with date formats in QlikView and QlikSense

rwunderlich — Tue, 18 Jul 2017 01:02:22 GMT

The epoch base in Qlik is Dec 30,1899; that is day 0. Day 1 is Dec 31, 1899.

Note that in XLS date 1 is January 1st 1900. Date 60 in XLS is February 29th 1900, a date that does not exist. Starting date 61 XLS and Qlik have the same number for the same dates. See https://support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-leap-year