Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
Jennell_McIntire
Employee
Employee

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.

0 Likes
2,072 Views
Anonymous
Not applicable

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
2,072 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.

2,082 Views
pavendhan
Partner - Contributor III
Partner - Contributor III

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

date and time.png

0 Likes
2,082 Views
Gabriel
Partner - Specialist III
Partner - Specialist III

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
2,082 Views
alex_nerush
Partner - Creator II
Partner - Creator II

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

0 Likes
2,082 Views
robert99
Specialist III
Specialist III

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
2,082 Views
robert99
Specialist III
Specialist III

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
2,082 Views