Qlik Community

Qlik Design Blog

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

Employee
Employee

Qlik Sense – Date & Time

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:

Excel.png

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

declare.png

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:

derive.png

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.

asset panel.png                date and time.png

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

18 Comments
Not applicable

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?

0 Likes
72 Views
mr_barriesmith
Contributor

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?

0 Likes
72 Views
robert99
Valued Contributor II

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

72 Views
Employee
Employee

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

0 Likes
72 Views
robert99
Valued Contributor II

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.

72 Views
arockiyaselvana
Contributor

Good post for knowledge. Thank you for sharing this.

0 Likes
72 Views
alex_nerush
Contributor II

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.

0 Likes
72 Views
Employee
Employee

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

0 Likes
72 Views
haty
New Contributor III

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

0 Likes
72 Views
Not applicable

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.

0 Likes
72 Views
Not applicable

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

0 Likes
72 Views

Hi,  I am a bit late the party here.... but what is the best way to control how a date is displayed ?

I am changing the display format as per the script below.  Is this a sensible way ?  Is there a better way ?

Calendar:

DECLARE FIELD DEFINITION TAGGED '$date'

   Parameters

      first_month_of_year = 1

   Fields

      Year($1) As Year Tagged '$year',

      MonthName($1) as MonthYear Tagged '$monthyear',

      Date($1,'DD MMM YYYY') as Date Tagged ('$date', '$day'),

;

0 Likes
72 Views
Not applicable

I agree with RJ,

new function is handy but what's really missing is an automated way to create a canonical calendar/date.

72 Views
pavendhan
New Contributor II

I could not able to get the values for below image.

date and time.png

0 Likes
72 Views
Gabriel
Valued Contributor II

Hi Jennell,

I have a question around Groups in your script.

Does it matter if  TYPE COLLECTION is in upper case or lower case? See below

GROUPS

Year, Month, Date TYPE COLLECTION AS YearMonthDate;

OR

GROUPS

Year, Month, Date type collection as YearMonthDate;

I ask this question because the upper case wouldn't work as expected (v3.0).

0 Likes
72 Views
alex_nerush
Contributor II

Groups is not supported in DECLARE FIELD DEFINITION in accordance to documentation. See Declare ‒ Qlik Sense

0 Likes
72 Views
robert99
Valued Contributor II

Hi Bill

I have started using (or testing) this because of the Time Aware Charts  . But I didn't like the way the name was so long. So have renamed as follows

I linked it to the Canon Date and renamed to DateC (canon or combined)

And rather than using Calendar (too long) used |

DerivedDate.JPG

0 Likes
72 Views
robert99
Valued Contributor II

Hi David

Hopefully one day Qlik find a way to do this. Using say a date Island approach but would work like Henric's Canon date (can use in chart's and to select). But without the effort and issues to set the canon date.

0 Likes
72 Views