Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

A common question in the QlikCommunity forum is how to show only the last N months. The suggested answers are most of the time relatively complex set analysis expressions including dollar expansions with aggregation functions, e.g.

     Sum( {$<Date={">=$(=MonthStart(AddMonths(Max(Date),-12)))<$(=MonthEnd(Max(Date)))"}>} Sales)

Such an expression may work fine. However, it is not simple. When seen by someone who didn't write it, it is almost incomprehensible. So instead of such an expression, I would like to suggest a slightly different method: Relative calendar fields.

The idea is to define relative calendar fields in the master calendar. By doing this, it will be a lot simpler to define chart expressions. For example, you can in your master calendar define fields calculating how many days or months ago a specific date was:

     Today() - Date as DaysAgo,
     12*(Year(Today())-Year(Date)) + Month(Today()) - Month(Date) as MonthsAgo,

Then you will be able to have much simpler chart expressions, e.g:

     Sum({$<MonthsAgo={">=0<12"}>} Sales)

This expression does almost the same as the initial expression. But it is much easier to read and understand. Below you have a chart using this expression showing the last 12 months. (Financial year that starts in April; today is a day in June and the dimensions are rolling month [Date(MonthStart(Date),'MMM')] and fiscal year.)

Bar Chart Last 12.png

Further, you can also create fields for relative months within the financial year; fields that you can use for a year-on-year comparison:

     Today() - YearStart(Today(),0,$(vFM)) - Date + YearStart(Date,0,$(vFM)) as DaysAgoYTD,
     Mod(Month(Today())-$(vFM),12) - Mod(Month(Date)-$(vFM),12) as MonthsAgoYTD,

The variable vFM is the first month of the financial year, see more on Fiscal Year.

MonthsTable.png

If you have these fields defined, you can easily make a year-to-date chart comparing the different years. The expression will be the same as before, but with MonthsAgo changed to MonthsAgoYTD:

     Sum({$<MonthsAgoYTD={">=0"}>} Sales)

Below you have a chart of a year-over-year comparison (using the built-in accumulation):

Bar Chart YoY.png

Bottom line: By defining relative dates and months in your master calendar, you can significantly simplify your set analysis expressions. Also, such fields will help your users create new charts. And your charts may even evaluate faster...


Simplicity.

HIC

23 Comments
tseebach
Luminary Alumni
Luminary Alumni

Hi Henric,

Actually your set will potentially give you a wrong result. The correct expression is to specify the that you wnat the intersection of the dates like this:

Sum( {$<Date={">=$(=MonthStart(AddMonths(Max(Date),-12)))"}*

{"<$(=MonthEnd(Max(Date)))"}>} Sales)

Notice the * between the min and max dates.

In a very rare case, I found that writing the expression as you did, gave me a wrong result, I filed this to the support team, and I was told its a WAD, and the correct way is to use the intersection.

5,712 Views
hic
Former Employee
Former Employee

I don't quite see when that would happen, but you may still be right. If so, there is an additional reason to use relative months in the master calendar!

HIC

0 Likes
5,712 Views
Not applicable

This is a good example of how a lot of calculations can be simplified by creating supporting data in the model. This helps in reading the expression and also speeds up calculation times.

5,712 Views
swuehl
MVP
MVP

Again a nice post, Henric.

One question arose: You've said "This expression does the same as the initial expression."

Isn't there a subtle, but important difference, that your calculated relative calendar fields take today() as reference, while the original expression will take max(Date), thus is sensitive to current selection state?

I've seen a lot of applications where exactely this is needed (user selects reference date for comparision).

@Torben: Your comment sounds interesting, could you comment on the rare cases where you have seen a difference?

Only thing that came to my mind are cases where the evaluation of the lower and upper limit in the search expression are actually leading to an 'inverse range', something like >=10<=0. Here you will not get an empty set (AND search logic switched to OR).

5,712 Views
hic
Former Employee
Former Employee

Stefan

You are of course absolutely right. My expression assumes that the reference date is today, and not the user selection. If you want the reference date to be dynamic, then you must use a more complicated set analysis expression.

But I don't like too complex set analysis expresions, so I didn't want to encourage it...

In fact, I think that often, a too complex set analysis expression denies the user the right to make his own selection. If you want the user to see 12 consecutive months with an arbitrary reference date - why not just let him select that? No set analysis needed for that.

HIC

0 Likes
5,712 Views
paulyeo11
Master
Master

I strongly agree with stefan point out on user preference on user selection on prefer date.

As in actual case when we have a sales meeting to day let said 29 june ( current month not yet close ). and we need to see which SBU is not able to achieve the sales target till end of May 31 and not just use the max date today.

HIC

I have a question to you .

Can i said that SET analysis unable to perform below :-

Let said today 12 june 2013. and i need to use SET anaylsis to get sales from 1 jan 2012 till 12 june 2012 is not possible ?

Only YTD expression below is possible to achieve this :-


If(Num([TempDate]) >= $(vYearStart) and Num([TempDate]) < $(vMonthNow), -1, 0)


As YTD,

Paul

0 Likes
5,712 Views
hic
Former Employee
Former Employee

If you define the field "MonthsAgoYTD" as described in the blog post, you can do exactly this. The chart would look like the bottom chart and could have the following settings:

Dimension 1: Month (or Fiscal Month)

Dimension 2: Year (or Fiscal Year)

Expression: Sum({$<MonthsAgoYTD={">=0<12"}>} Sales)

To get only last year's data you would of course need to make a selection of "2012". If you instead want to hard-code this, you could instead have the following expression:

Sum({$<YearsAgo={'1'},MonthsAgoYTD={">=0<12"}>} Sales)

HIC

0 Likes
4,686 Views
paulyeo11
Master
Master

Hi HIC

Great that it is possible. I will invest my time to understand how does your code work , as this is what i am looking for vey long ago.

Paul

0 Likes
4,686 Views
Not applicable

Hi Henric,


Can you use this to create rolling 12 months?

Dion

0 Likes
4,686 Views
hic
Former Employee
Former Employee

"Rolling 12" usually means that you for a specific month want to display the number pertaining to this month and the previous 11. I.e. you need to include transactions not related to this month.

You can probably use this technique to simplify some calculations, but it does not solve the problem. To solve it, you need to use set analysis or some other technique to include the unrelated transactions in the calculations.

See more on Accumulative Sums and on Calculate rolling n-period totals.

HIC

0 Likes
4,686 Views