Date & Time Functions

These functions are used to transform the date and time values within Qlik Sense. Qlik Sense reads dates and processes these functions based on the numerical value of the date. The number represents days since the 30th of December 1899.

This article will cover the basic principles of working with date & time functions in the data load editor, including:

  • The general function syntax
  • Working with date functions in the data load editor
  • Working with date functions in charts

For a complete, up-to-date list of date & time functions, please see the Qlik Sense help pages here.

The date & time function syntax

The syntax will differ depending on the function. For up-to-date syntax, please do refer to the Qlik help pages here.

Understanding the function syntax given by Qlik Sense

Unfortunately, it isn’t easy to decipher the syntax provided in the help pages linked above if you aren’t used to reading it. In this section, we will explain how to read this syntax so that you can apply this thinking to every function in the help pages.

To explain the syntax, we will use a couple of different functions that have a few different syntax elements and decipher them in turn.

Example 1: AddMonths

This function adds the specified number of months to a given date.

Copy code 


AddMonths(startdate, n , [ , mode])

The rules for reading this are as follows:

  1. The function name itself is first and all the parameters you need to enter go between brackets: ()
  2. The various elements you will need to enter are separated by commas. In this function, there are 3 elements: the startdate, n and the [mode]
  3. Anything between square brackets [] is an optional parameter. In this function, [mode] is an optional parameter which you only need to include if you want to count days to the end of the month instead of from the start. Use 1 for mode in this case. If you do include it, DO NOT write it in brackets – just write the number 1. The default value if you don’t enter anything is 0 but you can also enter 0 if you wish.

An example of this function being used with the optional parameter included could be:

Copy code 


AddMonths(Today(),-6,1)

The above function will return whatever the date was 6 months ago from today but the day date may be slightly different if we are in a 31 day month and 6 months ago was a 30 day month. This is because we are counting days from the end of the month, not the start, by using the optional parameter.

An example of the function without using optional parameters may be:

Copy code 


AddMonths(Today(),-6)

This code would now return the exact date 6 months ago from today, e.g. if we are on the 5th Jun 2023, the date returned will be 5th Dec 2022.

Example 2: YearName

This function returns a dual value: the display will be the 4 digit year of a given date but the underlying numerical value is the timestamp of the first millisecond of the year of the given date. This is important for sorting purposes.

Copy code 


YearName(date[, period_no[, first_month_of_year]] )

The rules for reading this are as follows:

  1. The function name itself is first and all the parameters you need to enter go between brackets: ()
  2. The various elements you will need to enter are separated by commas. In this function, there are 3 elements: the date, [period_no] and the [first_month_of_year].
  3. Anything between square brackets [] is an optional parameter. In this function, both, the [period_no] and [first_month_of_year] are optional parameters. You need to enter a value for [period_no] if you need to return a year before or after the year in your ‘date’. You need to enter [first_month_of_year] as an integer if you want to start your year in month other than January. Do not be confused by the double set of square brackets – the syntax is written like that because two optional parameters follow each other. If you do include them, DO NOT write the brackets – just the relevant values.

An example of this function being used with the optional parameter included could be:

Copy code 


YearName('05/06/2023',-1,2)

The above function will return 2022-2023 because we have specified that we want to go back a year and we want to start our year in February. When the [first_month_of_year] is specified, the result will always be two years.

An example of the function without using optional parameters may be:

Copy code 


YearName('05/06/2023')

This code would now return 2023.

How to use date & time functions in the data load editor

Now that you understand how to read the syntax of Qlik functions, you need to understand how to use these functions in the load. The rules are:

  • The functions can be used throughout the whole load script, not just within a LOAD or SELECT statement
  • They are particularly useful for creating calendars
  • The functions can be nested and combined with other types of functions
  • They can be used in the ‘where’ clause of the LOAD or SELECT statements to restrict the date range of the data brought in

A very basic load using a date function in the ‘where’ clause may look something like this:

Copy code 


CUSTOMER_ORDERS_LAST_6M:
LOAD
   "Customer Name",
   Avg("Order Value") as "Average Order Value"

FROM [my_data.qvd] (qvd)

WHERE
	"Order Date" >= AddMonths(Today(),-6)

GROUP BY 
   "Customer Name";

The loaded table will include a list of customer names in the first field and their average order value over the last 6 months in the second.

Another simple example could be using the functions to create a calendar:

Copy code 


SALES:
LOAD
	"Date",
    "Product Code",
    "Quantity",
    "Total Value"
    
FROM [my_data.qvd] (qvd);

CALENDAR:
DECLARE FIELD DEFINITION TAGGED '$date'
Fields      
      Year($1) 		as Year Tagged ('$numeric'),
      Month($1) 	as Month Tagged ('$numeric'),
      Date($1) 		as Date Tagged ('$date'),
      Week($1) 		as Week Tagged ('$numeric');
      
DERIVE FIELDS FROM FIELDS [Date] USING CALENDAR;

The field “Date” will now become x4 date fields that you can use in visualisations: year, month, date and week.

How to use date & time functions in visualisations

Using date & time functions in visualisations is very similar to using them in the load.

  • The functions can be nested and combined with other types of functions.
  • They can be used as dimensions or used to create measures.
  • They can be used within any visualisation types.

Here is an example of using the Year() and Month() functions to create a YoY comparison line chart from a given set of dates. The functions used are in the axis labels.