Qlik Community

Qlik Sense Enterprise Documents

Documents for Qlik Sense related information.

Dealing with date formats in QlikView and QlikSense

dominicmander
Contributor

Dealing with date formats in QlikView and QlikSense

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#().

Comments

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

dominicmander
Contributor

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.

Not applicable

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?

dominicmander
Contributor

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

shanemichelon
Contributor

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.

sarahplymale
Contributor

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

dominicmander,


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

Capture.PNG

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')



dominicmander
Contributor

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.

eirikmag
New Contributor

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?

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

Version history
Revision #:
1 of 1
Last update:
‎01-25-2016 01:17 PM
Updated by: