A Use-Case For The Possible – P() – In Qlik

In the latest instalment of the Ometis How-To Guides, our man Chris Lofthouse answers a customer’s question on a bespoke calendar with a use-case for the possible in Qlik Sense.  

Last week, I had an interesting question from a customer I thought I’d share. The customer in question had a bespoke calendar, using a 53-week calendar year, primarily use week-based analysis and periods following a 5-4-4 week structure. Their question was as follows:

How do I perform like-for-like comparative analysis of one year vs another, regardless of the user selecting the year, quarter, period, week or day of week, etc?

The main consideration was that one year’s date was not necessarily the same period, week or day of week the next year.

For example:

04-Feb-2019 fell into the first day of the week, week 6, period 2, quarter 1.

04-Feb-2018 fell into the seventh day of the week, week 5, period 1, quarter 1.

Here we have the classic, ‘same date last year but not the same day last year’! Over the years I’ve come up with countless solutions for this and I’m sure you have too. However, the solution I favour most makes use of the possible values function – P().

P([FieldName]) represents all the possible field values in the specified field.

Before we can use the possible function, I start by adding the last year date equivalent to each date. If we take the example above, 04-Feb-2019 would have the last year date equivalent of 05-Feb-2018. A simple and efficient way to achieve this is to map the last year date equivalent into the calendar record.

For example:

[MAP_LastYearDateEquivalent]

MAPPING LOAD

[Financial year]+1&’~’&[Financial period-week]&’~’&  [Day of week number] AS [Comparison period], //Add one to the year so it maps to following year.

[Date] AS [Last year date equivalent]

FROM [Calendar table];

An evaluated example has been filled out below:

[MAP_LastYearDateEquivalent]

MAPPING LOAD

2019&’~’&P2W6&’~’&1 AS [Comparison period], //2019 = 2018 + 1

2018/02/05 AS [Last year date equivalent]

FROM [Calendar table];

As a result of the common field values, i.e. 2019, P2W6, 1, the example above (2018/02/05) will map to the record for the date 2019/02/04. Remember we are adding this as an additional field in the calendar.

Now we can reference the last year date equivalent in our SET expressions.

To do this, add the following to your SET expression:

[Date field]=P([Last year date equivalent])

If your users are likely to select the year and month fields, you will need to ignore these selections as they will conflict.

Full example expression:

SUM({<[Year], [Month], [Date field]=P([Last year date equivalent])>} [Sales amount])

That’s it!

The benefit of this solution is that it keeps the SET expression relatively minimal, it provides a lot of flexibility for end users to make selections at a mixture of granularities and the last year date equivalent is calculated once, during the reload.

What do you think about this use-case for the possible? As always, let me know your thoughts in the comment section below or on social media. And, please remember to like, share and follow if you enjoy the content.

By Chris Lofthouse
Follow @clofthouse89