trueChart Help

Using time dimensions in Qlik Sense

Owned by Derrich Schleich
Jan 26, 2023

In order to use TRUECHART’s ability to format dates for time-based dimensions/charts, the supplied dates must be formatted in Qlik Sense to match this expression: year + quarter + month + week + day, where every part is optional.

  • year is the four-digit year

  • quarter is the one-digit quarter of the year (1–4)

  • month is the two-digit number of the month (01–12)

  • week is the two-digit number of the week (01–53)

  • day is the two-digit day of the month (01–31)

Assembling the date strings using expressions

dimensions

In order to put together a date string according to the syntax mentioned above, use the following Sense functions (assuming Date being the date column to format) and glue them together using the & (ampersand) operand:

Date part

Function

Year

year(Date)

Quarter

ceil(month(Date)/3)

Month

num(month(Date),'00')

Week (non-ISO)

num(week(Date,FirstWeekDay,1),'00')

Day

num(day(Date),'00')

Caution

TRUECHART doesn't work well together with the ISO 8601 specification of week numbers which allows the first week of the year to begin in the old year and the last week of the old year to continue into the new year as the convention doesn't support a separation of 'actual year' and 'year the week belongs to'. Keep that in mind when making week-based reports.


Examples

  • =year(Date)&num(month(Date),'00') creates a string containing year and month.

  • =year(Date)&num(month(Date),'00')&num(day(Date),'00') creates a string containing year, month and day.

  • =year(Date)&ceil(month(Date)/3) creates a string containing year and quarter.

See also