Qlik Community

Qlik Design Blog

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

The Date Function

One Qlik function that occasionally causes confusion is the Date function. I have often seen errors caused by an incorrect usage of it, so today I will try to explain what the function does – and what it does not.

Interpretation vs Formatting

The first thing you should be aware of is that there are two different functions: Date#() and Date(). The first is an Interpretation function and the second is a Formatting function.

 

  • Interpretation functions use the textual value of the input, and convert this to a number.
  • Formatting functions use the numeric value of the input, and convert this to a text.

 

In both cases, the output is a dual, i.e. it has both a textual value and a numeric value. The textual value is displayed, whereas the numeric value is used for all numerical calculations and sorting.

 

The table below shows how to use the interpretation function Date#(). Note that the format code must match the input parameter.

 

Interpretation.png

 

This is very different from the formatting function Date(). Next table shows how to use this function. Note that the format code matches the format of the output text.

 

Formatting1.png

 

In real life, it is often useful to nest an interpretation function inside a formatting function:

 

Nested.png

 

Formatting vs Rounding

The second thing you should be aware of is that the Date() function and other formatting functions never change the numeric value of the input value.

 

This means that you can format a timestamp as a date only, without the time information. This can sometimes be confusing since there is a “hidden” value. In the table below, you can see that the input value corresponds to 12:00 in the middle of the day, but the Date() function effectively hides this from the textual output - but it remains in the the numeric value.

 

Formatting2.png

 

So what should you do if you want to remove the time part of the field, and just keep the date part? Well, obviously you must use a function that changes the numeric value: You need a Rounding function, e.g. DayStart() or Floor().

 

In the table below, you can compare the output of the Date() function with a couple of different rounding and formatting options.

 

Rounding.png

 

Summary

The above discussion is not relevant to dates only. It is just as relevant for Years, Weeks, hours, seconds and any other time interval. Further, it is relevant to a number of other functions:

 

Interpretation functions: Date#(), TimeStamp#(), Time#(), Interval#(), etc.

Formatting functions: Date(), TimeStamp(), Time(), Interval(), etc.

Rounding functions: Round(), Floor(), Ceil(), DayStart(), WeekStart(), MonthStart(), etc.

 

Combine these functions sensibly, and you will be able to round or format any way you want.

 

HIC

 

Further reading related to this topic:

Get the Dates Right

Why don’t my dates work?

34 Comments
Partner
Partner

Thanks HIC

Another brilliant blog.

0 Likes
3,481 Views
Luminary
Luminary

Good blog, Henric.

I remember the first time i ran across this issue was trying to attach a Master Calendar to a dataset for the first time.  At the time, i had no idea that the Master Calendar contained flat dates, and that my dataset also contained what i thought were dates.  Except i had forgotten to floor() them to remove the timestamp!  Many hours later and lots of cursing, i realised my mistake.

A cautionary tale, and definitely one to highlight the poignancy of this blog.

0 Likes
3,481 Views
bestofwest
Contributor II

I don't have this kind of problems because I just use the Date format to display.


On edit Script I put the Date field in number format using Database function for example

or input files contains already previous transformation.


Date(0) is 1899-12-31

0 Likes
3,481 Views

@ Antonio Caria

In other words: You use non-formatted integers to represent dates in your data model. This is a method that works very well.

However, you don't lose anything when you format these integers as dates using the Date() function. It will still be the numeric value that is used to link data. So, I prefer to format these integers as dates - because that is what they really are. The important thing is to make sure that they are integers.

HIC

PS: Date(0) is 1899-12-30

0 Likes
3,481 Views
bestofwest
Contributor II

I use numeric values due performance questions.

Try to load 10 M records or more and You will see the difference...

Otherwise on Qlik View I don't need to do conversions or use Date()/Date#() functions.

0 Likes
3,481 Views

There should be no difference in performance between an integer key formatted as an integer, and an integer key formatted as a date.

HIC

0 Likes
3,481 Views
bestofwest
Contributor II

Only if you use QVD input data without any Date Conversion / Transformation.

AC

0 Likes
3,481 Views
Not applicable

Henric, thank you for the post. I find them very educational.  Please keep it up.

0 Likes
3,481 Views
paulyeo11
Valued Contributor II

Hi Henric

Are you the one who develop those function ?

I just wonder why you know so much ?

Paul

0 Likes
3,481 Views
Employee
Employee

Henric thank you - i don't think i'm exagerrating when i say that this topic and solutions come up a dozen times every day on the community blog.  QlikView AND now Qlik Sense.

0 Likes
3,481 Views
Not applicable

Henric,

Nice Post..

Mohan

0 Likes
3,481 Views
robert99
Valued Contributor II

Thanks Henric

Dates completely confused me when I first started using QV. I eventually found solutions but by trial and error. Now I finally understand (and I read all your previous blog posts on dates).

0 Likes
3,481 Views
Partner
Partner

A crystal clear blog. Very well explained!

0 Likes
3,481 Views
Not applicable

Thanks for the post ,

Regards,

Mahesh

0 Likes
3,481 Views
Not applicable

thank u

0 Likes
3,481 Views
Not applicable

How to Multiply Previous Rows in a Table

Hi, Could you help on this Problem? Thank you very much!

0 Likes
3,481 Views
Not applicable

1. "... , and convert this to a number.".

but

"...the output is a dual, i.e. it has both a textual value and a numeric value".

2. "..., and convert this to a text".

but

"...the output is a dual, i.e. it has both a textual value and a numeric value".

???

I can use it but I am not sure that I understand the layers here.

0 Likes
3,481 Views

Both functions are duals, i.e. they both return a textual and a numeric value.

The textual input of the Date#() function will be used as textual output. In addition, the numeric interpretation of the text will be used as numeric value.

The numeric input of the Date() function will be used as numeric output. In addition, the textual formatting of the number will be used as textual value.

HIC

0 Likes
3,481 Views
Not applicable

thx, but there must like a "master and slave" concept in this. For instance what does the table viewer tag tells us, so for '#' the numric value becomes the "master"? Though still it is dual...   

0 Likes
3,481 Views

You could say that for the Date#() function, the textual input is the master; and for the Date() function, the numeric input is the master.

Graphical explanation.png

In the Date#() function, the textual input is used (as-is) as output, and the numeric output is created (transformed) from the textual input. And the opposite is true for the Date() function.

HIC

3,481 Views
Not applicable

Thx, a very nice explanation Though I got the transformation part. What I think I dont fully understand is what happens after that. From the output stage, right side and further on. If I do not specify anything, which one does it take. In the table viewer, when I hover the field, why does it not tell me both tags, text and num?  So I mean it seems like there is a "standard" that it use, while the other is in a "background layer"?

Also if it was truly dual I should have been able to do Date(Date#()) in one function, just specifying both formats?        

0 Likes
3,481 Views

First of all, it is a truly dual value. You can nest Date#() inside Date(). The only reason why the nesting is not implemented as single one function is that the two functions use different format codes, and it is clearer what you do if you use two functions. Input is perhaps YYYYMMDD and output should perhaps be M/D/YY.

Which value QlikView uses? That depends on where you use the dual field.

  • The text is always used for display.
  • The number (if it exists) can be used for sorting.
  • String functions and operators, e.g. Left(<dual_value>) use the textual part
  • Numeric functions and operators, e.g. Mod(<dual_value>, n) use the numeric part.

HIC

3,481 Views
Partner
Partner

Can you help me understand what is happening in this code?

Trim(Date(Timestamp_Field)) as Timestamp_Removed

Either in the load script or in the UI, this removes the timestamp portion from the underlying serial number. I am trying to understand how Trim(), a text function, alters the numeric portion of a field in this way.

I found this in some existing code and after explaining that Trim() only removes leading/trailing spaces, found that it in fact does the same thing as Floor() in this context. (I created a sample .qvw but can't attach it to this reply.)

0 Likes
3,481 Views

Not quite.

  1. The Date() function removes the time portion from the string representation.
  2. The Trim() function removes the numeric part of the dual as well as leading/trailing spaces.

... and you're left with a string that looks like a date. Then the automatic date interpretation kicks in, so it gets its numeric value back.

Try

   Timestamp(Trim(Dual(Date(Timestamp_Field,'YYYYMMDD'),Timestamp_Field)))

and you'll see.

HIC

3,481 Views
lawrenceiow
Contributor II

Thank you for this very clear Blog, hic. My problem with dates has been down to my difficulty understanding the QlikView help screens/User guide.  What does it take to get the "help screen" writers to consult with you before they put in their examples?

3,481 Views
Partner
Partner

Ah, I had it all but the automatic date interpretation of the resulting string. Thank you!

0 Likes
3,481 Views
sebbrunie1
New Contributor

Hello,

I would like to have in text object the date with format 16th of may 2015. How can i have the "th" after the day?

Kind Regards

Sébastien

0 Likes
3,481 Views
sspe_dgs_com
Contributor II

I don't think you can get this from a build in function in QV, so you might have to build your date as a string where you add the 'xx' to the date.

Just be aware that you'll have to check the date, because 'th' is not used for all date. It's e.g. the 1st. , 2nd, 3rd, 4th., ...21st....31st., so it's not all 'th'.

Regards

Steen

0 Likes
3,481 Views
lawrenceiow
Contributor II

update: See below

@sebbrunie1

My understanding is the same as sspe_dgs.com. - you will have to create a text (string) value.  If it helps, this is the rather cumbersome expression I use in my script.

First, the map...

ordMap:

Mapping LOAD * Inline [

From,To
1, 1st
2, 2nd
3, 3rd
]
;

and then the expression...

if(len(day(DateRecorded))=1,ApplyMap('ordMap',day(DateRecorded),right(day(DateRecorded),1)&'th'),left(DateRecorded,1)&ApplyMap('ordMap',right(day(DateRecorded),1),right(day(DateRecorded),1)&'th')) as OrdinalDay

Update 16-05-2016:

I look at this today and wonder how long have I been doing this so badly wrong! As mentioned by sspe_dgs.com‌ (Steen Schlüter Persson) below, it just a simple applymap for the exceptions:

ApplyMap('ordMap',day(DateRecorded),day(DateRecorded)&'th') as OrdinalDay

...and, of course, the missing lines from the map itself...

21, 21st

22, 22nd

23, 23rd

31, 31st

0 Likes
3,481 Views
sspe_dgs_com
Contributor II

Along the same line as the above suggestion, you can also try this script -

set vDateStart = '01-04-2015';
set vDateEnd = '01-05-2015';
 
DateMap:
Mapping LOAD * Inline [
From,To
1, 1st
2, 2nd
3, 3rd
21, 21st
31, 31st
  ]
;
 
[Date]:
 
LOAD *, ApplyMap('DateMap',DAY(Temp_Date), DAY(Temp_Date)&'th') &' '& MonthName(Temp_Date)&' '& Year(Temp_Date) AS NewDate ;
LOAD
date( date#('$(vDateStart)','DD-MM-YYYY')-1 + recno() ,'DD-MM-YYYY') as Temp_Date
AUTOGENERATE (date#('$(vDateEnd)')-date#('$(vDateStart)'))+1
;

It's simply using a Map table to hold all the "exception" dates (the ones that shouldn't have "th" applied). If a match is found using the ApplyMap it will use the "To" value from the Map table - otherwise it will apply "th" as the default.

You can add this in your load script where you load your Date dimension so you have it for all dates.

/Steen

3,481 Views