Date and time functions

Qlik Sense date and time functions are used to transform and convert date and time values. All functions can be used in both the data load script and in chart expressions.

Functions are based on a date-time serial number that equals the number of days since December 30, 1899. The integer value represents the day and the fractional value represents the time of the day.

Qlik Sense uses the numerical value of the parameter, so a number is valid as a parameter also when it is not formatted as a date or a time. If the parameter does not correspond to numerical value, for example, because it is a string, then Qlik Sense attempts to interpret the string according to the date and time environment variables.

If the time format used in the parameter does not correspond to the one set in the environment variables, Qlik Sense will not be able to make a correct interpretation. To resolve this, either change the settings or use an interpretation function.

In the examples for each function, the default time and date formats hh:mm:ss and
YYYY-MM-DD (ISO 8601) are assumed.

Note:

When processing a timestamp with a date or time function, Qlik Sense ignores any daylight savings time parameters unless the date or time function includes a geographical position.

For example, ConvertToLocalTime( filetime('Time.qvd'), 'Paris') would use daylight savings time parameters while ConvertToLocalTime(filetime('Time.qvd'), 'GMT-01:00') would not use daylight savings time parameters.

Use the drop-down on each function to see a brief description and the syntax of each function. Click the function name in the syntax description for further details.

Integer expressions of time

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.

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.

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.

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

This function finds if a timestamp falls within the part a period of the month, bi-month, quarter, tertial, 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.

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 1 January as the first day of the week.

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.

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.

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

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

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, tertial, or half-year containing a base date.

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

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.

This function returns
a value corresponding to a timestamp of the first millisecond of the
lunar week containing date. Lunar weeks in Qlik Sense are defined by counting 1 January as the first day of the week.

This function returns
a value corresponding to a timestamp of the last millisecond of the
lunar week containing date. Lunar weeks in Qlik Sense are defined by counting 1 January as the first day of the week.

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 1 January as the first day of the week.

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.

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.

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.