Qlik Sense – Date & Time

[Jennell_McIntire]

One of the new features release on Monday with Qlik Sense 1.1 is the ability to generate date and time fields.  Now, you may be thinking that you always generate date fields in your applications – I know that I do – but in Qlik Sense 1.1, we have introduced the Declare and Derive statements that make it easier for you to create a calendar definition that you can use for all date fields in your application.  This is brilliant and easy to do.

I tested it out by loading some employee expense data that looks something like this:

I then used the Declare statement to create a calendar definition.

I named the definition Calendar and tagged it as $date.  I indicate what the first month of the year should be and then I list the fields that I want generated by the definition.  In this example, I entered Year, Month, Date and Week.  I could enter others if I want here like Day and Time.

Last, I entered one group that will create a drill down for Year, Month and Date and I named it YearMonthDate.  I could list other groups here as well if I need them.  In this definition $1 represents the data field from which the date fields will be generated.  In this example, that will be the ExpenseDate field.

Now that the calendar definition is created, I just need to use the Derive statement to apply the calendar to the date field that I have already loaded.  In this example, the field is named ExpenseDate and my Derive statement looks like this:

If I had more date fields that I had loaded in my data model, I could apply the calendar definition to all of them in the Derive statement by separating the field names with commas.  In my Derive statement I used specific data fields but there are alternatives as well.  You can also derive fields for all fields with a specific tag or for all fields with the field definition tag.  You can find examples of these in Qlik Sense Help.

Once this is complete, simply reload the app.  Now when you go into the Fields tab in the Assets panel, you will see a tab for Date & time fields and when you expand it, you will see the date fields that were generated by the calendar definition.

               

These date fields can be used like you usually use them in your applications – as filters, in visualizations and so on.

I recommend you try it out and refer to Qlik Sense Help for details if you need help.  It will save you time especially if you have an application with a lot of date fields that you would like to build out into a calendar.  You create the calendar definition one time and in one statement (the Derive statement), you can list all the fields that the definition should be applied to (that you want to generate date fields for).  Reload and the calendar is done.  Did I already say that this is brilliant!

Thanks,

Jennell

[#2]

Thank you for your post. It really helps a lot.

I did it but I was not able to use the Group YearMonthDate. Where it is available to use?

[mr_barriesmith]

It is a great feature - thanks Qlik.  Used a dual() to create year-month and was initially concerned about the field names but then you can provide a proper name via the Library or master list.

Question to Qlik: these fields are hidden in the data model viewer... Are they there physically? Anyone done volume testing?

[robert99]

Whats the reason for the

tagged '$year' etc

And are canonical dates still possible?

Canonical Date

Edit. It would be great if a filter could called Month (not ExpenseDate.Calendar.Month) that would filter all the different Month types as if a canonical date has been set up.

Otherwise I can't really see that this is huge benefit (unless there are a large number of dates that a calendar is needed for). It only takes  a few minutes to set up a calendar for a date. And it clearly shows on the data model viewer as a user defined field name. eg Month.Exps rather than ExpenseDate.Calendar.Month.

But the canonical date is a bit tricky

Thanks

[hic]

If you want to use derived fields on a canonical date, you still need to create the bridge table for the canonical date; i.e. you need to create the necessary date field. However you don't need to create any additional calendar tables.

You say that you don't see the huge benefit. Well, the benefit is that you don't need to create calendar tables in the data model. So, if you consider it easy to create calendar tables, this feature does not mean a great simplification. But for users who think scripting is tricky and may not even know that they need a master calendar, the derived fields is a huge benefit.

HIC

[robert99]

Thanks for the reply

I was hoping that this process described above would allow a common (canonical) date selection as if a canonical date had been set up. So a filter (selection) box could be set up for ExpenseDate.Calendar.Month or just  Month. Month would filter all dates as a canonical date would (but maybe this is not possible to do. I assume it would be essential to indicate what field to filter by in the expression  eg Date = {ExpenseDate} ).

I like canonical dates (I wish I has understood this as explained in your excellent blog post on this topic before I had set up a number of files at work)  but it is little tricky to set up.

Agree with the benefit. i will try this at work next week and see what I think.

[arockiyaselvana]

Good post for knowledge. Thank you for sharing this.

[alex_nerush]

Thanks for the post. One question. Where is your group? I don't see any group in the list of fields. You have specified that you created  a drill down for Year, Month and Date and named it YearMonthDate.

[Jennell_McIntire]

A few people have asked about the group that I created and where it can be found.  While the script supports the creation of the group, any groups created in the script will currently not show up in the Asset Panel.  Sorry for the confusion.  I will update this post once the groups are showing up in the Asset Panel.

Thanks,

Jennell

[haty]

This awesome, thanks Jennell. I can't wait to try this out.

[#11]

Hello Jennell,

I have used Date and Time feature. Its working for me. I am finding some issues while using created fields with set expression.

I have posted it in discussion

expression issue with qliksense 1.1.0 Date & time fields

Please have a look.

Thanks.