Announcements
Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: Register
Former Employee

## Year-over-Year Comparisons

A number alone doesn't tell you very much – you need to compare it with something. And very often you want to compare this year’s number with last year’s.

It is called Year-over-Year (YoY).

In such a comparison, you can for example compare the sales of the current month with the sales for the same month last year. Or – if you want to avoid fluctuations due to good or bad months, you instead look at the accumulated sales in the current year compared the same period last year. Then you look at the Year-to-Date (YTD) number.

But how do you calculate it? How do you write a simple formula that picks out a subset of transactions from last year and compares them to the corresponding transactions from the current year?

If you have Month as dimension and show accumulated numbers in the chart, you don’t need to do anything. The numbers are comparable as they are.

However, if you don’t use Month as dimension, the numbers will no longer be comparable since last year contains transactions from a longer period. You still may want to make the comparison, but with another first dimension and Year as the second.

There are several ways to do this, and they differ in how the reference date is defined. One way is to let the user define an arbitrary reference date – either through a selection or through a variable – and then use this is an advanced Set Analysis expression.

Another, much simpler way is to use the date of the script run as reference date. If your application is refreshed every night, this would mean that the Year-to-Date calculation always is up until today’s date.

Here’s how you do it:

In your Master Calendar you should define flags – Boolean fields – that define whether or not a specific date should be included in the calculation:

If( DayNumberOfYear(Date) <= DayNumberOfYear(Today()), 1, 0 ) as IsInYTD,

The above formula tests whether the date falls before today’s date or not. Note that this flag will be useful also for dates belonging to other years than the current. The value of the flag will be 1 for dates in the beginning of the year irrespective of which year it is.

Then you can use this flag in a simple Set Analysis expression:

Sum( {\$<IsInYTD={1}>} Amount )

The Set Analysis expression will pick out the correct dates and thus the correct transactions for the comparison. Further, this expression can be combined with any dimensions.

Flags for a number of different time periods can be created like this, not just Year-to-Date, but also Quarter-to-Date, Month-to-Date, Current Month, Last Month, etc.

If( DayNumberOfQuarter(Date) <= DayNumberOfQuarter(Today()), 1, 0) as IsInQTD,

If( Day(Date) <= Day(Today()), 1, 0) as IsInMTD,

If( Month(Date) = Month(Today()), 1, 0) as IsCurrentMonth,

If( Month(AddMonths(Date,1)) = Month(Today()), 1, 0) as IsLastMonth,

Summary: Create the necessary flags in your Master Calendar. It will simplify your Set Analysis expressions tremendously.

HIC

Tags (6)
Partner - Creator III

hi HIC,

really superb discussion.

18,716 Views
MVP

Nice post, will try to implement today in my current apps.

18,716 Views
Partner - Master

Hello HIC,

how about the idea to change your formula

Sum( {\$<IsInYTD={1}>} Amount )

to

Sum( Amount*IsInYTD )

In cases that IsInYTD is 1 the Amount is multiplicated with 1 and is the value itself, on the other case it is multiplicated with 0 and is 0.

What is the high-performance formula?

Regards

18,716 Views

Thanks Henric - another post that is sure to be useful for people, Year on Year comparison is something I see people requesting help with a lot on the Community.

I have uploaded a couple of prior period examples in the past, including this one:

QlikView App: Set Analysis - Prior Period Comparison

18,716 Views
Partner - Creator

Very good Henric... introduce in my work.

18,716 Views
Employee

I usually use other functions:

 if(InYearToDate(Date, \$(vCurrentDate), 0), 1, 0) AS [Cal IsInYTD], if(InYearToDate(Date, \$(vCurrentDate), -1), 1, 0) AS [Cal IsInLastYTD] if(InQuarterToDate(Date, \$(vCurrentDate), 0), 1, 0) AS [Cal IsInQTD] if(InQuarterToDate(Date, \$(vCurrentDate), -1), 1, 0) AS [Cal IsInLastQTD]

Are there any difference in behaviour using this instead of your formulas?

Another point, this solution does not allow to use Year-Month as a dimension and show a full history. My colleague Loïc Formont adressed this by creating a Calendar-Link-Table between the Master Calendar and the Facts. The expressions become something like Sum({<[Date Type] = {'YTD'}, [Year Shift] = {-1}> Sales}

His solution is quite complex to write down but he has a demo app if you are interested

18,716 Views
Specialist

Very good post. Thanks.

14,854 Views
Specialist II

Nice post. I've been using something simlar combined with heat maps to get user/server info into a dashboard.

14,854 Views
Former Employee

The Sum( Amount*IsInYTD ) will work - in principle.

It is however slower than Sum( {\$<IsInYTD={1}>} Amount ). And further, the same construction cannot be used for other aggregation functions, such as Count() and Avg(). Better then to use Set analysis which is fast and you can use in any aggregation function.

HIC

14,854 Views
Former Employee

I haven't seen the entire solution, but I get the picture. The flags that you list will work also, but there is one major difference: Your flags can only be used for one year at the time.

So, if you want to compare last year with current year, you must make two expressions. Whereas with my flags, I can choose if I want one common expression:

Sum( {\$<IsInYTD={1}>} Amount )

or two separate, one per year:

Sum( {\$<IsInYTD={1}, <IsLastYear={1}>} Amount )

Sum( {\$<IsInYTD={1}, <IsCurrentYear={1}>} Amount )

HIC

14,854 Views
Subscribe by Topic