Date and time functions

This function returns an integer representing the second when the fraction of the expression is interpreted as a time according to the standard number interpretation.

second(expression)

This function returns an integer representing the minute when the fraction of the expression is interpreted as a time according to the standard number interpretation.

minute(expression)

This function returns an integer representing the hour when the fraction of the expression is interpreted as a time according to the standard number interpretation.

hour(expression)

This function returns an integer representing the day when the fraction of the expression is interpreted as a date according to the standard number interpretation.

day(expression)

This function returns an integer representing the week number according to ISO 8601. The week number is calculated from the date interpretation of the expression, according to the standard number interpretation.

week(expression)

This function returns a dual value: a month name as defined in the environment variable MonthNames and an integer between 1-12. The month is calculated from the date interpretation of the expression, according to the standard number interpretation.

month(expression)

This function returns an integer representing the year when the expression is interpreted as a date according to the standard number interpretation.

year(expression)

This function returns the year to which the week number belongs according to ISO 8601. The week number ranges between 1 and approximately 52.

weekyear(expression)

This function returns a dual value with:

• A day name as defined in the environment variable DayNames.
• An integer between 0-6 corresponding to the nominal day of the week (0-6).

weekday(date)

This function returns a timestamp of the current time. The function returns values in the TimeStamp system variable format. The default timer_mode value is 1.

now([ timer_mode])

This function returns the current date. The function returns values in the DateFormat system variable format.

today([timer_mode])

This function returns a timestamp of the current time for a specified time zone.

localtime([timezone [, ignoreDST ]])

This function returns a date calculated from the year YYYY, the month MM and the day DD.

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

This function returns a date calculated from the year YYYY, the week WW and the day of week D.

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

This function returns a time calculated from the hour hh, the minute mm, and the second ss.

maketime(hh [ , mm [ , ss [ .fff ] ] ])

This function returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate.

addmonths(startdate, n , [ , mode])

This function returns the date occurring n years after startdate or, if n is negative, the date occurring n years before startdate.

addyears(startdate, n)

This function finds if the input timestamp falls within the year of the date the script was last loaded, and returns True if it does, False if it does not.

yeartodate(date [ , yearoffset [ , firstmonth [ , todaydate] ] ])

This function returns the time zone, as defined on the computer where the Qlik engine is running.

timezone( )

This function returns the current Greenwich Mean Time, as derived from the regional settings.

GMT( )

Returns the current Coordinated Universal Time.

UTC( )

Returns the current adjustment for daylight saving time, as defined in Windows.

daylightsaving( )

Converts a UTC or GMT timestamp to local time as a dual value. The place can be any of a number of cities, places and time zones around the world. 

converttolocaltime(timestamp [, place [, ignore_dst=false]])

This function takes as input a timestamp and a year and updates the timestamp with the year specified in input.

setdateyear (timestamp, year)

This function takes as input a timestamp, a month and a year and updates the timestamp with the year and the month specified in input.

setdateyearmonth (timestamp, year, month)

This function returns True if timestamp lies inside the year containing base_date.

inyear(date, basedate , shift [, first_month_of_year = 1])

This function returns True if timestamp lies inside the part of year containing base_date up until and including the last millisecond of base_date.

inyeartodate (date, basedate , shift [, first_month_of_year = 1])

This function returns True if timestamp lies inside the quarter containing base_date.

inquarter (date, basedate , shift [, first_month_of_year = 1])

This function returns True if timestamp lies inside the part of the quarter containing base_date up until and including the last millisecond of base_date.

inquartertodate (date, basedate , shift [, first_month_of_year = 1])

This function returns True if timestamp lies inside the month containing base_date.

inmonth(date, basedate , shift)

Returns True if date lies inside the part of month containing basedate up until and including the last millisecond of basedate.

inmonthtodate (date, basedate , shift)

This function finds if a timestamp falls within the same month, bi-month, quarter, four-month period, or half-year as a base date. It is also possible to find if the timestamp falls within a previous or following time period.

inmonths(n, date, basedate , shift [, first_month_of_year = 1])

This function finds if a timestamp falls within the part a period of the month, bi-month, quarter, four-month period, or half-year up to and including the last millisecond of base_date. It is also possible to find if the timestamp falls within a previous or following time period.

inmonthstodate (n, date, basedate , shift [, first_month_of_year = 1])

This function returns True if timestamp lies inside the week containing base_date.

inweek(date, basedate , shift [, weekstart])

This function returns True if timestamp lies inside the part of week containing base_date up until and including the last millisecond of base_date.

inweektodate (date, basedate , shift [, weekstart])

This function determines if timestamp lies inside the lunar week containing base_date. Lunar weeks in Qlik Sense are defined by counting January 1 as the first day of the week., Apart from the final week of the year, each week will contain exactly seven days.

inlunarweek (date, basedate , shift [, weekstart])

This function finds if timestamp lies inside the part of the lunar week up to and including the last millisecond of base_date. Lunar weeks in Qlik Sense are defined by counting January 1 as the first day of the week and, apart from the final week of the year, will contain exactly seven days.

inlunarweektodate (date, basedate , shift [, weekstart])

This function returns True if timestamp lies inside the day containing base_timestamp.

inday(timestamp, basetimestamp , shift [, daystart])

This function returns True if timestamp lies inside the part of day containing base_timestamp up until and including the exact millisecond of base_timestamp.

indaytotime(timestamp, basetimestamp , shift [, daystart])

This function returns a timestamp corresponding to the start of the first day of the year containing date. The default output format will be the DateFormat set in the script.

yearstart( date [, shift = 0 [, first_month_of_year = 1]])

This function returns a value corresponding to a timestamp of the last millisecond of the last day of the year containing date. The default output format will be the DateFormat set in the script.

yearend( date [, shift = 0 [, first_month_of_year = 1]])

This function returns a four-digit year as display value with an underlying numeric value corresponding to a timestamp of the first millisecond of the first day of the year containing date.

yearname(date [, shift = 0 [, first_month_of_year = 1]] )

This function returns a value corresponding to a timestamp of the first millisecond of the quarter containing date. The default output format will be the DateFormat set in the script.

quarterstart(date [, shift = 0 [, first_month_of_year = 1]])

This function returns a value corresponding to a timestamp of the last millisecond of the quarter containing date. The default output format will be the DateFormat set in the script.

quarterend(date [, shift = 0 [, first_month_of_year = 1]])

This function returns a display value showing the months of the quarter (formatted according to the MonthNames script variable) and year with an underlying numeric value corresponding to a timestamp of the first millisecond of the first day of the quarter.

quartername(date [, shift = 0 [, first_month_of_year = 1]])

This function returns a value corresponding to a timestamp of the first millisecond of the first day of the month containing date. The default output format will be the DateFormat set in the script.

monthstart(date [, shift = 0])

This function returns a value corresponding to a timestamp of the last millisecond of the last day of the month containing date. The default output format will be the DateFormat set in the script.

monthend(date [, shift = 0])

This function returns a display value showing the month (formatted according to the MonthNames script variable) and year with an underlying numeric value corresponding to a timestamp of the first millisecond of the first day of the month.

monthname(date [, shift = 0])

This function returns a value corresponding to the timestamp of the first millisecond of the month, bi-month, quarter, four-month period, or half-year containing a base date. It is also possible to find the timestamp for a previous or following time period.The default output format is the DateFormat set in the script.

monthsstart(n, date [, shift = 0 [, first_month_of_year = 1]])

This function returns a value corresponding to a timestamp of the last millisecond of the month, bi-month, quarter, four-month period, or half-year containing a base date. It is also possible to find the timestamp for a previous or following time period.

monthsend(n, date [, shift = 0 [, first_month_of_year = 1]])

This function returns a display value representing the range of the months of the period (formatted according to the MonthNames script variable) as well as the year. The underlying numeric value corresponds to a timestamp of the first millisecond of the month, bi-month, quarter, four-month period, or half-year containing a base date.

monthsname(n, date [, shift = 0 [, first_month_of_year = 1]])

This function returns a value corresponding to a timestamp of the first millisecond of the first day of the calendar week containing date. The default output format is the DateFormat set in the script.

weekstart(date [, shift = 0 [,weekoffset = 0]])

This function returns a value corresponding to a timestamp of the last millisecond of the last day (Sunday) of the calendar week containing date. The default output format will be the DateFormat set in the script.

weekend(date [, shift = 0 [,weekoffset = 0]])

This function returns a value showing the year and week number with an underlying numeric value corresponding to a timestamp of the first millisecond of the first day of the week containing date.

weekname(date [, shift = 0 [,weekoffset = 0]])

This function returns a value corresponding to a timestamp of the first millisecond of the first day of the lunar week containing date. Lunar weeks in Qlik Sense are defined by counting January 1 as the first day of the week and, apart from the final week of the year, will contain exactly seven days.

lunarweekstart(date [, shift = 0 [,weekoffset = 0]])

This function returns a value corresponding to a timestamp of the last millisecond of the last day of the lunar week containing date. Lunar weeks in Qlik Sense are defined by counting January 1 as the first day of the week and, apart from the final week of the year, will contain exactly seven days.

lunarweekend(date [, shift = 0 [,weekoffset = 0]])

This function returns a display value showing the year and lunar week number corresponding to a timestamp of the first millisecond of the first day of the lunar week containing date. Lunar weeks in Qlik Sense are defined by counting January 1as the first day of the week and, apart from the final week of the year, will contain exactly seven days.

lunarweekname(date [, shift = 0 [,weekoffset = 0]])

This function returns a value corresponding to a timestamp with the first millisecond of the day contained in the time argument. The default output format will be the TimestampFormat set in the script.

daystart(timestamp [, shift = 0 [, dayoffset = 0]])

This function returns a value corresponding to a timestamp of the final millisecond of the day contained in time. The default output format will be the TimestampFormat set in the script.

dayend(timestamp [, shift = 0 [, dayoffset = 0]])

This function returns a value showing the date with an underlying numeric value corresponding to a timestamp of the first millisecond of the day containing time.

dayname(timestamp [, shift = 0 [, dayoffset = 0]])

The age function returns the age at the time of timestamp (in completed years) of somebody born on date_of_birth.

age(timestamp, date_of_birth)

The networkdays function returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holiday.

networkdays (start:date, end_date {, holiday})

The firstworkdate function returns the latest starting date to achieve no_of_workdays (Monday-Friday) ending no later than end_date taking into account any optionally listed holidays. end_date and holiday should be valid dates or timestamps.

firstworkdate(end_date, no_of_workdays {, holiday} )

The lastworkdate function returns the earliest ending date to achieve no_of_workdays (Monday-Friday) if starting at start_date taking into account any optionally listed holiday. start_date and holiday should be valid dates or timestamps. 

lastworkdate(start_date, no_of_workdays {, holiday})

This function calculates the day number of the year in which a timestamp falls. The calculation is made from the first millisecond of the first day of the year, but the first month can be offset.

daynumberofyear(date[,firstmonth])

This function calculates the day number of the quarter in which a timestamp falls. This function is used when creating a Master Calendar.

daynumberofquarter(date[,firstmonth])