QlikView date and time functions are used to transform and convert date and time values. All functions can be used in both the 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.
QlikView 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 QlikView 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, QlikView 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.
Information note
When processing a timestamp with a date or time function, QlikView 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 minute when the fraction
of the expression is interpreted as a time
according to the standard number interpretation.
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.
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.
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 with 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 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).
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.
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.
This function finds if a timestamp falls within the part of 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.
This function returns true if timestamp lies inside the
lunar week
containing base_date. Lunar weeks in QlikView are defined by counting 1 January as the first day of the week.
This function returns true if timestamp lies inside the
part of the lunar week up to and including the last millisecond of base_date. Lunar weeks in QlikView are defined by counting 1 January as the first day of the week.
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.
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.
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.
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.
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 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.
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.
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, 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.
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 (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 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.
This function returns
a value corresponding to a timestamp of the first millisecond of the
lunar week containing date. Lunar weeks in QlikView 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 QlikView 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 QlikView 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.
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.
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.
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.
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.
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.
Modernize without compromising your valuable QlikView apps with the Analytics Modernization Program. Click here for more information or reach out: ampquestions@qlik.com