Qlik Sense Date and Time Functions – Syntax and Example

1. Objective

In our last Qlik Sense tutorial, we discussed Qlik Sense Integer Functions. Today, we will see Qlik Sense Date and Time Functions. We use the date and time functions in Qlik Sense to process and evaluate the date and time data values present in the data records. Basically, we use these functions both in the script and chart expressions. Each time and date value since December 30, 1899 has a unique serial number (i.e. number of days from December 30,1899) whose integer part represents the day and fractional value represents the time of that day. So, when Qlik Sense evaluates these functions, the date and time values are taken up as numerical values.

So, let’s start Qlik Sense Date and Time Functions.

Qlik Sense Date and Time Functions – Syntax and Example

Have a look at Qlik Sense Mathematical functions 

2. Qlik Sense Date and Time Functions

Following are the Qlik Sense Date and Time Functions, let’s discuss them in detail –

i. Timestamp functions

These functions return timestamps in specific situations.

a. Now() function

The Now() function returns the current time as a timestamp from the system’s clock. This function returns value in dual data type.

Syntax:

now([timer_mode])

Where the timer_mode can assume three values, i.e. 0 (returns the time of last completion of data load), 1 (returns the time of a function call) and 2 (returns the time when the app was opened). The default value is set at 1.

For example,

• now( 0) will return the time when the last data load completed.
• now( 1) will return the time of the function call. If and when you use this function in a current load script, then it returns the current time of a function called within that data load.
• now( 2) will return the time when the app was opened.

b. Today() function

The today() function returns the current date in the timestamp from the system’s clock. This function returns the value in dual data type.

Do you know about Qlik Sense Null Functions

Syntax:

today([timer_mode])

Where the timer_mode can assume three values, i.e. 0 (returns the time of last completion of data load), 1 (returns the time of a function call) and 2 (returns the time when the app was opened). The default value is set at 1.

For example,

• today(0) will return the day when the last data load completed.
• today(1) will return the day of the function call. If and when you use this function in a current load script, then it returns the current day of a function called within that data load.
• today(2) will return the day when the app was opened.

c. Localtime() function

This function returns the current time as a timestamp according to a specific time zone or the local time zone of the system’s clock.

Syntax:

localtime([timezone [, ignoreDST ]])

Where, through timezone the place of the timezone is specified in strings e.g. ‘Paris’ or as in the ‘GMT+hh:mm’ notation.

By setting ignoreDST as -1 i.e. True, the daylight savings time adjustment will be ignored.

For example,

• localtime() will return the local time 2017-10-22 12:54:47 (according to your system’s clock).
• localtime(‘London’) will returns the local time in London like 2017-10-22 11:54:47.
• localtime(‘GMT+02:00’) will return the local time in the timezone of GMT+02:00 like 2017-10-22 13:54:47.
• localtime(‘Paris’,’-1′) will return the local time in Paris with daylight savings time ignored like, 2017-10-22 11:54:47.

Let’s learn more about Qlik Sense Box Plot

ii. Make functions

The make functions make a date, week dates or time according to the provided parameters.

a. Makedate() function

This function makes a date out of the given year, month and day parameters as YYYY, MM, DD respectively.

Syntax:

makedate(YYYY [ , MM [ , DD ]])

For example,

Makedate(2018,10,6)

Returns the date 2018-10-06.

b. Makeweekdate() function

This function returns the date from the given week information such as YYYY (year), WW (number of week) and D (day of the week, where 0 is assumed as Monday) as parameters.

Syntax:

makeweekdate(YYYY[, WW[, D]])

For example,

makeweekdate(2014,6,6) will return 2014-02-09.

makeweekdate(2014,6) will return 2014-02-03 as weekday is assumed as 0 (Monday).

Know more about Qlik Sense Scatter Plot

c. Maketime() function

This function creates a date from the given parameters such as hh (hours), mm (minutes) and ss (seconds). If you mention no minute or second value in specific, then it is assumed to be 00.

Syntax:

MakeTime(hh[,mm[,ss]])

For example,

• maketime(20) will return the time as 22:00:00
• maketime(20,15) will return the time as 20:15:00
• maketime(20,15,30) will return the time as 20:15:30

iii. Set Time Functions

a. Setdateyear() function

The setdateyear function returns an updated year by taking a timestamp and a specific year as parameters of the function. This function returns a dual data type.

Syntax:

setdateyear(timestamp, year)

Where timestamp is the date and year a four-digit year entered by you.

For example,

You want to update a list of dates to that of the current year.

SetYear:
Load *,
SetDateYear(testdates, 2018) as NewYear
Inline [
Testdates
1/5/2017
2/1/2017
19/5/2017
15/9/2017
11/12/2017
2/3/2017
14/5/2017
13/6/2017
7/7/2017
4/8/2017
];

In the resultant table, the year 2017 will be changed to 2018.

b. Setdateyearmonth() function

This function updates the year and month of a date in a timestamp. This function returns a value of dual data format.

Let’s discuss Qlik Sense Color Functions

Syntax:

setdateyearmonth(timestamp, year, month)

For example,

we load the following dates and update both, the date and month of them.

SetYearMonth:
Load *,
SetDateYearMonth(testdates, 2018,2) as NewYearMonth
Inline [
Testdates
2/1/2017
19/5/2017
15/9/2017
11/12/2017
14/5/2017
13/6/2017
7/7/2017
4/8/2017
];

The resultant table will have dates with updated months and years.

iv. Other date functions

a. Addmonths() function

This function returns a date which will occur n months before (if n is negative) or after (if n is positive) the mentioned startdate. This function returns a data in dual format.

Syntax:

addmonths(startdate, n ,[,mode])

For example,

ddmonths(‘2017-06-29’,3) will return ‘2017-09-29’ as the date for 3 months later of the start date.

Have a look at Qlik Sense Counter Functions

b. Addyears() function

This function returns a date where n number of years has been added to the startdate. If n is positive then, n years after the startdate are added and if n is negative, then n years before the start date is added to the current date.

Syntax:

AddYears(startdate, n)

For example

• addyears(‘2010-01-29’,3) will return the date ‘2013-01-29’
• addyears(‘2010-01-29’,-1) will return the date ‘2009-01-29’

c. Yeartodate() function

This function returns a Boolean value (True or False) for whether the year mentioned in the function falls on the same year as recorded in the date of the timestamp for when script was last loaded.

Syntax:

YearToDate(timestamp[,yearoffset[, firstmonth [,todaydate]] ])

Where the timestamp is the date that this function will evaluate.

By yearoffset the function evaluates the presence of same period in some other year than what is mentioned in the timestamp. If the value of this parameter is negative, then the function will look for the pervious year’s date and if it is positive, then the next year’s date. If you do not specify this parameter, then it is considered to be 0.

firstmonth parameter specifies a month by the value 1 to 12 which decides from which month a year must start. For instance, you set this parameter to 5 if you want the year to start from May1st which is a fiscal year.

todaydate sets the first day of a month.

For example,

If the last script reload time was ‘2018-10-30’

Then the function will return,

• yeartodate(‘2017-10-30’)  returns False.
• yeartodate(‘2018-09-12’) returns True because the years match
• yeartodate(.2017-10-30’, 1) returns True because the year offset is set at 1 which gives the year to follow i.e. 2018 in this case.

Let’s learn more about Qlik Sense Logical Functions

So, this was all in Qlik Sense Date and Time Functions. Hope you like our explanation.

3. Conclusion

Hence, in this Qlik Sense Date and Time Functions Tutorial, we discussed all the important date and time function that we use to handle and work with dates and time in your data which is a very crucial information for the proper functioning of any enterprise.

Still, if you have any query regarding Qlik Sense Date and Time Functions ask in the comment tab.

See also – 

Treemap Visualization in Qlik Sense 

Reference for Qlik Sense