Qlik Community

Qlik Design Blog

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

Announcements
Attachments getting stuck in the virus scanner. The team is investigating.

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
9,141 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
9,141 Views
Creator II
Creator 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
9,141 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
9,141 Views
Creator II
Creator 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
9,141 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
9,141 Views