Qlikview Month Over Month Comparison Essay

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.

 

 

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 ) asIsInYTD,

 

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) asIsInQTD,

   If( Day(Date) <= Day(Today()), 1, 0) asIsInMTD,

   If( Month(Date) = Month(Today()), 1, 0) asIsCurrentMonth,

   If( Month(AddMonths(Date,1)) = Month(Today()), 1, 0) asIsLastMonth,

 

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

 

HIC

Hello friends,

 

For a couple days now I have been trying to come up with a solution to my problem but I cannot seem to find the answer.  Maybe one of you can help.

 

In summary, I have a set of data which contains Date, Spend ($$), and Clicks (#).  On a single chart I want to be able to display the total Spend and Clicks for two different periods, however, I have 4 different period comparisons I need to handle; displaying the type of comparison based on user choice.

 

1)    Week over week (any week of any year)

  a.    12/1/10 – 12/7/10 over 12/1/11 – 12/7/11 (different years)

  b.    7/4/11 to 7/10/11 over 7/11/11 to 7/17/11 (same year)

 

2)    Month over month (any month of any year)

  a.    Aug 2010 over Aug 2011 (same month over different years)

  b.    Aug 2011 over Sept 2011 (consecutive months same year)

 

3)    QTR over QTR

  a.    Q1 2010 over Q2 2010 (consecutive quarters, same year)

  b.    Q1 2010 over Q1 2011 (different year, same quarter)

 

4)    Year over year (any year over any year)

  a.    2010 over 2011

 

 

What are your suggestions and ideas for how to handle this?  Is it even feasible to filter the same data on two different periods?

 

Thanks,

Shane S.

 

P.S. I am working with Tableau v6.0

0 comments

Leave a Reply

Your email address will not be published. Required fields are marked *