Year-over-Year Comparisons

[hic]

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

[stevedark]

Hi Clare,

Not knowing the content of your variables I can't say for sure what is going on here.

You may have a problem in that MONTH is only 1 to 12, rather than a continuous value, so unless you have code in the expression you will hit a problem - all previous years will go transparent at the current month also.

If you want to get a fully sequential month number you can do year(Date) * 12) + month(Date) as MonthID,

Not having MONTH in an aggregation may also be an issue, unless you have it as a dimension?  Even then I tend to use the Only function - to remind myself why I am not aggregating.

Steve

[Sajid_Mahmood]

Hi HIC,

Can you please send me the qvw sample file showing the below chart format and expressions used.

Many thanks for your quick response.

Regards

Sajid

[stevedark]

Hi Sajid,

There is nothing clever going on with this chart.  The expression would simply be sum(Sales) the trick is having two separate dimensions, rather than a single Month Year one.  As you can see from the image both Year and Month are dimensions on the chart.  You just need to promote one to get them the right way around.

If you only have a date, or month/year, field in your source data the Month and date can be obtained in the load script like this:

Year(DateField) as Year,

Month(DateField) as Month,

Alternatively, it is common practice to have a calendar script for these date breakdowns.  If you Google you will find lots of examples of these.

Hope that helps.

Steve

[hic]

I thought it was quite clever to turn the two dimensions around, to use Month as first dim and Year as second ...

... but you're right that there is nothing advanced with this chart:

Dim1: Month

Dim2: Year

Measure: Sum(Sales)

Accumulation: Full

And that's it!

HIC

[berryandcherry6]

Hi Henric,

While calculating YOY by month Can i use more than one Expression.

Because i am trying to calculate YOY by month In dimension i have Year and Month and In Expression

i have

fabs(count({<DateType = {invitation},

Canon_Year = {"$(=year(max(CanonicalDate)))"}>}invitation_id)-count({<DateType = {invitation},

Canon_Year = {"$(=year(max(CanonicalDate))-1)"}>}invitation_id))

But have to add one more expression,Add button is not getting highlighted to add.

[beck_bakytbek]

Thanks for sharing,

as always very helpful and informative

[#8]

hic‌

What if you are wanting to compare month over month, period over period, etc. raising a flag by a conditional format. For example if I want to compare sales period over period and the sales for the last two periods where sales are under XXX amount, then highlight that account in the table. We are wanting to look at these conditions over time, not just 'right now' but has this ever occurred in that past, if so...when?

Or an account has been purchasing a certain product over time.

[hic]

In that case you have two dates: You have the transaction date and you have a comparison date. The comparison date is like a deadline that moves in time.

Then the question could be something like: Is the accumulated sales amount for the last two months prior to the comparison date below XXX? If so, when did it happen? Show the comparison date!

This is a rolling sum that can be calculated e.g. by using an as-of table. Take a look at

The As-Of Table

Calculating rolling n-period totals, averages or other aggregations

HIC

[Anonymous]

I've got the same problem. Could you give me some more feedback so that I could use it too? I can't use a static period.

[jerifortune]

Hi team,

Could someone please, assist with how to calculate percentage increase month over month from the previous to current year. The LastYearSales and CurrentYearSales is calcuted using set analysis.

Month	LastYearSales	CurrentYearSales	PercentageIncrease
Jan	45	50	??
Feb
Mar

Thank you