Qlik Community

Qlik Sense Enterprise Documents & Videos

Documents & videos about Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More

Relative Time Periods (RTP)

camron_allan
New Contributor II

Relative Time Periods (RTP)

When it comes to aggregating measures over time, our users frequently have the following requirements:

  • To-Date ranges such as YTD and QTD
  • Relative time periods such as "Previous Year", "Previous Month", or “same period last year”
  • Rolling periods which are used for moving averages. (Ex. "Rolling 3 month average")
  • Year-over-Year growth metrics

I call these “relative time periods” (or “RTP” for short).  Although there are similar requirements at the week and day level, RTPs at the month level are by far the most common.  This blog post discusses only the month-level RTPs.

As any Qlik developer can attest, fulfilling these requirements can sometimes prove challenging. Although set expressions are very powerful, they can often prove troublesome.  Coding the above date logic can quickly make your set expressions hard to read.  The syntax highlighting in the IDE is not ideal and the expressions are notoriously hard to troubleshoot.

I’ve often found that a data-driven solution is best.  Adding a time dimension is a huge step in the right direction.  Those of us that have a background in data architecture will be familiar with time dimensions.  For the un-initiated, time dimensions are often referred to as “Master Calendars” in the Qlik forums.  In your model, the time dimension joins to your fact table via a date field.  For example the join might occur on [SalesHeader.OrderDate].

The typical time dimension has one row per date.  The remaining columns are attributes of the date.  You might represent your date in various numeric or text formats.  You typically break the date components into separate columns: day, month, and year.  You might have long and abbreviated month names.  You might have a column for quarter.  Don’t get me started with weeks!

The time dimension is very denormalized: it has one primary key and a lot of repeating values in the attribute columns.  All these time-related attributes make the report developer’s life easier, providing ample columns on which to filter, concatenate, etc.

To represent relative time periods, you need to create several fields which denote a date’s inclusion in a named range.  For example, you might have a “CurrentYTD” flag which contains a ‘1’ for all the dates between January 1st and today (the rest of the values are zero).  Now you can use a simple set modifier in your measure: Sum( {<CurrentYTD={1}>} Sales).  You can use this approach for other named ranges such as “Current Quarter”, “Rolling 3 months”, and “same period last year”.

This is a great approach, but now you need to generate your time dimension nightly; the flag values will shift as each day passes.  This isn’t a big deal.  We typically have apps that require nightly refresh.

So, you make a nice report that incorporates your new flags.  Everything looks great.  You have successfully delivered moving averages and year-over-year growth rates without your set expressions looking like a dog’s breakfast.  But then your user asks you for the same report “as at the end of last quarter.”  What?!!?

So what do you do now? Do you regenerate your time dimension based on an earlier day and reload your app?  Probably not.

Our requirement has just evolved: We now need RTPs, for EVERY month!

Fortunately, this can also be accomplished using a data-driven approach.  What’s more, we can leverage the time dimension we just created!

The solution is a mapping table which sits between the time dimension and the fact table.  It’s a many-to-many relationship that controls which dates are selected in fact based on two parameters:

  • An anchor date
  • A Relative Time Type (Ex. YTD, QTD, rolling 3 month, etc.)

So, now we can get any RTP by any anchor date!

Here’s a sample expression that yields YoY YTD Sales Growth as at April 2017.  I think it’s pretty readable.

(Sum( {<AnchorYearMonth={‘2017-04’}, RTP_Type={‘YTD’}>} Sales)

/ Sum( {<AnchorYearMonth={‘2017-04’}, RTP_Type={‘YTDPY’}>} Sales)) -1

Now, you can set your AnchorYearMonth to the current YearMonth or have it be selectable by the user. You could easily change the RTP_Type to current month or rolling 3 month to get similar year-over-year growth rates.

I’ve experimented with a number of approaches and this – by far – has been the easiest and most flexible to implement.  I’ve used it at several clients over the past 3 years.

I’ve created a reusable script library that makes it easy to create the mapping table based on your month dimension.  I encourage you to try out QlikRTP and let me know how it works for you.  It's available at GitHub.

QlikRTP

I credit Fabrice Aunez’s post on the Qlik Community forum for inspiration.

Simply create YTD, moving totals and comparisons versus Year Ago

Fabrice Aunez

Sep 17, 2013

Fabrice did a great job of describing his solution.  I internalized those concepts and endeavored to parameterize my scripts and make them easy for developers to incorporate into their models.

Note: QlikRTP currently works at the month level, although it can be adapted to work for relative weeks and days.

Version history
Revision #:
1 of 1
Last update:
‎2018-09-14 02:36 PM
Updated by: