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!