Qlik Sense Day Numbering Functions With Example

1. Objective

In our last Qlik Sense tutorial, we discussed Qlik Sense Date and Time Functions. Today, we will see Qlik Sense Day Numbering Functions. These functions return dates and numbers for specific cases of time and date mentioned in the functions. So, you can get these functions to calculate numbers like age, net work days, first and last work dates, day numbers of years and quarters from given information like timestamp (date and time), date of birth etc. In this lesson, we will learn about the day numbering functions in Qlik Sense in detail.

So, let’s start Qlik Sense Day Numbering Functions Tutorial.

2. Qlik Sense Day Numbering Functions

Following are some Qlik Sense Day Numbering Functions –

You must read Qlik Sense Time Zone Function

i. age() function

The age function returns the age of a person in years which is evaluated by the information given in the timestamp (which is the current date) and the date of birth of the person. This function returns a numeric value.

The syntax of Qlik Sense age function:

age(timestamp, date_of_birth)

For example, if you are using the DD/MM/YYYY date format then,

age(’25/01/2018′,’25/12/2010’) will return 6 as the person would complete 6 years and would have 7th year as running on the 25th of January,2018 which is the timestamp value.

Enter the script given below in the script editor of your Qlik Sense application and see what results you get.

Employees:
LOAD * INLINE [
Member|DateOfBirth
John|28/03/1989
Kacey|10/12/1990
Steven|5/2/1992
Samuel|31/3/1993
Chris|19/5/1994
Mike|15/9/1994
Kendal|11/12/1994
Jenny|2/3/1995
Katie|14/5/1996
Mathew|13/6/1996
Peter|7/7/1998
Emma|4/8/2000
] (delimiter is |);
AgeTable:
Load *,
age('20/08/2015', DateOfBirth) As Age
Resident Employees;
Drop table Employees;

This will result in three new fields, Member, DateOfBirth and Age which if you see in a table will look like this were all the ages are generated by the age() function which we used in the script above.

Do you know about Qlik Sense In Functions

Member	DateOfBirth	Age
John	28/03/1989	26
Kacey	10/12/1990	24
Steven	5/2/1992	23
Samuel	31/3/1993	22
Chris	19/5/1994	21
Mike	15/9/1994	20
Kendal	11/12/1994	20
Jenny	2/3/1995	20
Katie	14/5/1996	19
Mathew	13/6/1996	19
Peter	7/7/1998	17
Emma	4/8/2000	15

ii. networkdays() function

The networkdays() function returns the total working days i.e. Monday to Friday excluding Saturday. Sunday and any holidays are specified in the function expression.

Let’s discuss Qlik Sense Financial Functions

The syntax of Qlik Sense networkdays function:

networkdays(start_date, end_date [, holiday])

Where start and end date specify the time period for which you want to calculate the net working days. If you want to specify any holidays, you can do so by specifying the holiday dates in the expression like ’25/12/2013′, ’26/12/2013′, ’31/12/2013′, ’01/01/2014′ which are separated by commas.

For example,

networkdays ('19/12/2013', '07/01/2014', '25/12/2013', '26/12/2013')

This returns 12 as the net working days. It is so calculated that the holidays 25/12/2013 to 26/12/2013 are taken into account.

iii. firstworkdate() function

This function returns the first work date (i.e. start date) given the end date of work and net working dates are mentioned in the function as parameters. It also considers any holidays while evaluating the expression, if mentioned. It returns the values as integer.

The syntax for firstworkdate function:

firstworkdate(end_date, no_of_workdays {, holiday})

For example,

firstworkdate (’29/12/2017′, 10) will return 16/12/2017 as the first work date.

If you specify the holidays as well, it will be like,

firstworkdate (’29/12/2017′, 9, ’25/12/2017′, ’26/12/2017′) will return 15/12/2017 as the first work date where the dates 25/12/2017 and 26/12/2017 are holidays.

iv. lastworkdate() function

Contrary to the firstworkdate() function, the lastworkdate() returns the last date of work i.e. the end date of a time period based on the net working days. This function returns values in dual data type.

Let’s take a tour to Qlik Sense Logical Functions

The syntax  of Qlik Sense lastworkdate function:

lastworkdate(start_date, no_of_workdays {, holiday})

For example,

lastworkdate (’19/12/2017′, 9) expression will return 31/12/2017 as the last work date. Where, the date 19/12/2017 is the start date and 9 is the number of work days after which we want to know the end date or last work date. You also specify holidays in the expression separating the dates  by comma if more than one.

v. daynumberofyear() function

This function returns the day number from 366 days in a year based on the current date, timestamp or a date that is specified in the function. You can also make the function calculate the day nth day from a particular month’s start (i.e. 1st of that month) by specifying that month in numbers in the function. This function returns the value with integer as the data type.

The syntax of Qlik Sense day numbering functions:

DayNumberOfYear(timestamp[,start_month])

For example,

DayNumberOfYear(’12/09/2017′) returns 256 which the number of day this date falls on from the 1st of January 2017.

If you specify a month, then the result will be,

DayNumberOfYear(’12/09/2017′,3) returns 196 as the number of the day encountered starting from 1st of March.

vi. daynumberofquarter() function

As the 12 months in a year can be divided into three quarters, this function returns the number of the day a particular date is falling on in the quarter in which it resides.

The syntax of Qlik Sense Day Numbering Functions:

DayNumberOfQuarter(timestamp[,start_month])

Where timestamp is the date you want to know the day number in the quarter of and start_month is the number of month you want to set as the starting month for this evaluation. This function always returns an integer value.

You must read Qlik Sense Mathematical Functions

For example,

DayNumberOfQuarter('12/09/2017')

This will return 74 as the day number of the current quarter.

DayNumberOfQuarter('12/09/2014',3)

This returns 12 as the day number of the current quarter. The quarter will start from the 3rd month that is, the month of March.

So, this was all in Qlik Sense Day Numbering Functions. Hope you like our explanation.

3. Conclusion

Hence, we discussed all the Qlik Sense Day Numbering Functions. Thus, these functions are very useful in making calculations related to days and dates and save a lot of user’s time.

Share your feedback of reading Qlik Sense Day Numbering Functions with us.

See also- 

Qlik Sense Integer Functions

Reference for Qlik Sense