Qlik Community

Qlik Design Blog

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

Employee
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. 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.

Line chart months.png

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.

Bar chart Product.png

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

72 Comments
kalyandg
Contributor II

hi HIC,

really superb discussion.

0 Likes
97 Views

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

0 Likes
97 Views
martinpohl
Valued Contributor II

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

0 Likes
97 Views
MVP
MVP

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

97 Views
valerio_fatatis
New Contributor III

Very good Henric... introduce in my work.


0 Likes
97 Views
baa
Contributor

Hi Henric Cronström,

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

97 Views
MCampestrini
Valued Contributor

Very good post. Thanks.

0 Likes
97 Views
thornofcrowns
Valued Contributor II

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

0 Likes
97 Views
Employee
Employee

@ Martin Pohl

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

97 Views
Employee
Employee

@ Brice SACCUCCI

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

97 Views
mov
Esteemed Contributor III

I use the same method as Brice SACCUCCI

It has one essential advantage - the variable vCurrentDate (I prefer to call it vKPIDate) can be set to any date, which allows YoY and other "Period over Period" comparison using any date as a base date.

Yes, it means there is separate expression for each year.  Well, in my case it worth the effort...

Regards,

Michael

0 Likes
97 Views
nickm
New Contributor

Henric,

What is the simplest script solution for calculating Last Closed Period for current year.

Thanks

Nick

0 Likes
97 Views
carlos_reyes_qv
Valued Contributor

Very interesting alternative, I always use Set Analysis based on the Max Date selected or possible, but this option allows to compare non continuous years.

0 Likes
97 Views
baa
Contributor

Yes Henric, you're right. Each solution has its advantages and drawbacks. I guess it depends on what the business/user needs are, as usual : have the Year as a dimension, or not.

If you have 10 years of data, this can clutter the Chart and you would have to create a new dimension containing the current year and the previous one or go back to the 2-expressions solution.

In the whole picture, the intermediate Calendar-Link-Table is more evolved than what I exposed and is used to do more stuff. For instance, be able to chose between cumulative/single amounts and/or deal with different fact granularities (as you do in your Generic Keys whitepaper).

97 Views
Not applicable

I was trying to implement this once in my application but realized that in our apps if user selects 2013 YTD should be for 2013 and PYTD for 2012 not 2014 and 2013. then i decided to with sets only.

0 Likes
97 Views
Not applicable

Nice Post HIC

0 Likes
97 Views
perhemst
New Contributor II

Do you also set accumulation to "Full accumulation" or how do I get the accumulated numbers?

If so I get the problem with the accumulation just becoming a straight line beyond current month.

0 Likes
97 Views

Hi Brice, I would be very much interested in the full history demo application of your colleague. Sounds like it would solve the overlapping expressions problem in a year-month graph, using a LinkTable technique that sounds similar to the one I used in a pivot table P&L

0 Likes
97 Views
cabhijit
Valued Contributor

Very good alternative to work with dates.

Perhaps, Candidate for implementation in  my next assignment.

Thank you Henric.

0 Likes
97 Views
Not applicable

Hi Henric,

Very interesting article. I am currently having difficulties achieving a periodic analysis. Would you / anyone else be able to take  a look at my thread: http://community.qlik.com/thread/109539

Many thanks!

0 Likes
97 Views
sudeepkm
Valued Contributor III

Hi Henric, Thanks a lot for such a nice post. It is useful to most of us as YOY is a very common requirement in dashboard development.

0 Likes
97 Views
baa
Contributor

Hi Peter,

I'm working on this. Please wait a little bit so that I can post something. This can be tricky to correctly apply because using the wrong selections on the "technical" fields can lead to unexpected results!

0 Likes
97 Views
Not applicable

Good as always!

0 Likes
97 Views
Not applicable

Hi HIC

Thank you for every month you are able to give us a big surprise. as every month i have sales meeting , and i am able use those new technique i learn from you to present my data , so the meeting is not so boring.

As usual i try to implement , but my chart for both 2013 and 2014 stop at march month. i see your chart for previous year is able to plot from jan till dec. where did i go wrong ?

Re: Chart not able to display till Dec for YOY compare from HIC on recent blog post

Paul

97 Views
Not applicable

I like these flags in the Master Calendar, however, they do limit you to YOY comparison for only current year and last year because they are static.  I prefer to use a dynamic option such as max() or addyears() in the application so when the user selects a given year, the previous year is added as comparison or multiple years can be compared.

97 Views
Not applicable

Henric Cronström

Hi,

Can you please advise how to get last five years YTD sum on bar chart, where the dimension is year. The problem is if I select month, then it is giving correct YTD for the last max year only and then full year sum for the last years when using 2nd formulas as below.

I am using two formulas:

1) To get last five years sum

Sum({$<Year={">=$(=Max(Year)-4)<=$(=Max(Year))"}>} Sales Amount)

2) Then to get YTD, I modified above formula as:

Sum({$<Year={">=$(=Max(Year)-4)<=$(=Max(Year))"},Month=,Quarter=,Date={"<=$(=MonthEnd(max(Date)))"} Sales Amount)

Thanks in advance!

BR,

SAK

0 Likes
97 Views
MVP
MVP

Hi Shoaib,

I've responded to this question over on this thread: http://community.qlik.com/docs/DOC-4313

Cheers,

Steve

0 Likes
97 Views
Not applicable

What if my data is based on Month-Year, not a specific date? So an example data point is Apr 2011 or Apr 2012.  How do I create a year over year comparison for YTD data by month? So the bar chart would have 2011 data; the first data point would be YTD performance as of January, then YTD as of February and on through December compared to YTD for each month in 2012, 2013, and 2014 the same way.

0 Likes
97 Views
Employee
Employee

There is really no principal difference from if you have dates. I would load the months like dates and format them as months, e.g.

     Date( Date#( [Month-Year], 'MMM YYYY'), 'MMM YYYY'), as [Month-Year]

and then you would have them as a correct date serial number (1st day of month) but formatted as Month-Year, and you could do pretty much everything as you do with dates.

HIC

0 Likes
97 Views
Not applicable

When I put that into my script, it still brings back ‘Apr 2011’, not a serial number. I am new to QlikView so sorry if my questions seem simple. ☺

0 Likes
97 Views