Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Relative Calendar Fields

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
Luminary
Luminary

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.

1,766 Views

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
1,766 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.

0 Likes
1,766 Views
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).

1,766 Views

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
1,766 Views
paulyeo11
Valued Contributor III

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
1,766 Views

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
1,766 Views
paulyeo11
Valued Contributor III

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
1,766 Views
Not applicable

Hi Henric,


Can you use this to create rolling 12 months?

Dion

0 Likes
1,766 Views

"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
1,766 Views
Not applicable

Henric,

Thank you very much for the useful blog.

I have now added the following to my Calendar script

MonthName(today()) as This_Month,

MonthName(AddMonths(today(),-1)) as Prev_Month

Now I need to be able to show in a pivot or streight table, a total of sales for clients ONLY when there is a difference exceeding +-20% in this month's sales compared to last months'.

What do you suggest is the best way?

Thanks for any help. I've tried to put together some set analysis expressions, but it is not working...

Cheers,

fcrysss


0 Likes
1,766 Views
Employee
Employee

Dion

I use a flag in my master calendar specifically for rolling 12 mth (and prior rolling 12 mth). First set a variable vToday based on todays date, then add the lines below to the master calendar final load. This code ignores the current 'incomplete' month.


if(TempDate>=monthstart(AddMonths($(vToday), -12)) AND TempDate<monthstart($(vToday)), 1, 0) as Rolling12MthFlag,

if(TempDate>=monthstart(AddMonths($(vToday), -24)) AND TempDate<monthstart(AddMonths($(vToday), -12)), 1, 0) as RollingPrior12MthFlag
0 Likes
1,766 Views
Luminary
Luminary

Good post, it's always a good idea to make things as simple as possible for yourself!

Regarding time periods relative to the selected date, I use generated sequence numbers for days, weeks, months, quarters, etc. to make those expressions a lot easier. For example:

Month: Seq

Jan-12: 1

Feb-12: 2

Mar-12: 3

.....

.....

Dec-12: 12

Jan-13: 13

Feb-13: 14

Mar-13: 15

etc.

Instead of having to write all sorts of complicated set analysis statements, especially when crossing years, now if I want to get the rolling 6 months, I can just use:

sum( {<MonthSeq={"<=$(#=max(MonthSeq))>$(#=max(MonthSeq)-6)"}>} Revenue)

Add that statement to a variable named s.Month.Rolling6 and you can reuse it everywhere:

sum( {<$(s.Month.Rolling6)>} Revenue)

Notice that I left the set enclosure out of the variable, so that you can add additional modifiers if necessary.

For more information, the whole thing is quite well explained by Miguel Garcia in his "Magic of Set Analysis" blog post: http://iqlik.wordpress.com/2010/11/27/the-magic-of-set-analysis-point-in-time-reporting/

1,766 Views
Not applicable

As I'm very new to Qlikview, this is a great help. One question though, how would I create a WeeksAgo calendar field? My best guess was

52*(Week(Today())-Week(TempDate)) + Week(Today()) - Week(TempDate) as WeeksAgo

but this doesn't seem to work

0 Likes
1,766 Views

The Week() function "restarts" at 1 every year, so you cannot use this if the two dates span two years. No, I would probably do

     (WeekStart(Today()) - WeekStart(Date))/7

instead. To understand what this does you need to understand how QlikView handles dates. Read about this here. But to make a long story short: The WeekStart() function returns an integer corresponding to the day. So by taking the diff and dividing by 7, you get the number of weeks ago.

HIC

0 Likes
1,766 Views
Not applicable

Perfect. Thank you so much!

0 Likes
1,766 Views
Not applicable

Excellent post and a great deal of wisdom shared.

Many thanks!!

0 Likes
1,766 Views
Luminary
Luminary

Hi Henric, Here's a post the creates an 'AsOf' Calendar which can be used to create simple set analysis

http://community.qlik.com/docs/DOC-6593

Regards

Richard

1,766 Views
Not applicable

Excellent post

Thank you

0 Likes
1,766 Views
sudeepkm
Valued Contributor III

Hi Barry,

Are there any way we can use only rolling months across all years. The sequence might not work in that case. It is like taking all months in to a cycle or circle than a sequence or st. line.

For example if current month is Jan then data shows Jan till Apr (4 rolling months) and if the current month is Oct then Oct till Jan across all the available years. Creating a Flag would be great.

Thanks

0 Likes
1,766 Views

For sequencing, you cannot use the cyclic months. You need to create a sequential month, e.g. Monthstart(Date). See Cyclic or Sequential?

But generally if you want to create rolling averages, it could be a good idea to create an As-Of table. See The As-Of Table

HIC

0 Likes
1,766 Views
sudeepkm
Valued Contributor III

Hi Henric,

Thanks so much for the direction. Your blogs are really great.

My requirement was to default set "Effective Month" (a date field in my data model) to current month and next three months across all the years forward and backward.

The logic that I had before was comparing the Effective month >= current month and < current Month +3. It was not working for Nov and Dec months as it was going beyond 12. Using Sequence was not helping as it was restricting to current year or max year.

now I'm using addmonths() to find next three months and it works.

vCurr=subfield(Date(Today(),'M/DD/YYYY'),'/','1')

vTrg==subfield(Date(Addmonths(Today(),3),'M/DD/YYYY'),'/','1')

And then in Set analysis:

[Eff Mth]={">=$(vCurr)<$(vTrg)"}

Thanks and Regards,

Sudeep

0 Likes
1,766 Views
robert99
Valued Contributor II

Hi

I used MonthsAgo to show the closing SELECTED inventory balance and the previous month

As shown on this thread.

Re: Inventory Closing

Where a user selects one month and the closing inventory balance shows for this month and the previous 2 (or whatever)

So user selects the appropriate MonthsAgo. (or selects / filters by the appropriate MonthYear or month and Year works as well)

sum ({<MonthsAgo = { ">=$(=MonthsAgo)"  }, AuditMthYr  >}

Audit_Qty* Stock_Cost_Price)

To select the previous Month

sum ({<MonthsAgo = { ">=$(=MonthsAgo+1)"  }, AuditMthYr  >}

Audit_Qty* Stock_Cost_Price)

AuditMthYr = MonthYear

I was also asked to put the date in the heading

'FTF%' & ' ' & Month  & Year & ' ' & '(CallC, FSR=1)'

0 Likes
1,766 Views