Saturday 10 October 2015

Tableau Tip - Comparing a custom date range with the prior year

Here's a quick tip based on a problem that I solved with a colleague this week. Take the following scenario:

You want to see sales for a date range which you can select as you like, and compare that with the same date range one year earlier. And here's the catch - the date range you pick can be over a year. So for example you might select Feb 2015 to Aug 2015 and compare that with Feb 2014 to Aug 2014 OR you might select Jan 2014 to Apr 2015 and compare that with Jan 2013 to Apr 2014. So the periods can overlap.

Here's how we solved it:

To see the sum of sales for the current custom period, use parameters - one for start date and one for end date. In our case all data was registered at month start and we were doing months only. Because of this we didn't want to see the calendar appear so we used a list of dates created in Excel and copy and pasted in to the parameter. Example using Superstore Sales below, note US date format:

























And then duplicate the 'Start Date' parameter and name it 'End Date'.

Now what I often like to do when I want to bring back data that meets a certain criteria (in this case fit a date range, but could be 'is within a region' etc...) is create a calculated field that returns the value if the criteria is met and 0 if its not met. This means it can still be aggregated however you like and you don't have to worry about the potential conflict or impact of filtering. So in this case we wrote the following calculated field for the current period sales



Notice how the IF statement is completely inside the aggregation - that's something you can use in many situations including much more complicated calculations. And again in our example, all data was at month level.

Now how about the prior period, one year back? Well because the periods can overlap and can be any period we can't use one of the standard table calculations, at least I don't think so. So we made use of the DATEADD function with a value of 1 to offset the order date by a year, so that we pick up prior data. Like so

A quick line plot shows that the calculations are doing what we expect


And now we can very easily do comparative calculations of current period versus prior period, for example to see % growth across categories



hope you find that useful.



4 comments:

  1. Thank you for confirming that this is currently the best (only?) way to do this. It's comforting knowing we're on the right track! We have used similar logic, and because in certain visualizations we want to see 4 week, 12 week, and 52 week chunks simultaneously, we have additionally created individual calculated fields for each of those time periods. (If we didn't need to see them simultaneously, we could just use a parameter for number of weeks and be done with it.)

    ReplyDelete
    Replies
    1. Hi Cathy, I can't guarantee this is the best way, someone smarter than me might have a better idea, but at least its a way that works :-)

      Delete
  2. This is exactly what I am looking for, only have spent most of the morning trying to find it online elsewhere. Next time, I need to come here first!

    ReplyDelete
  3. You are a genius! Damn. Fixed my problem! Thanks. Bookmarking this page. <3

    ReplyDelete

Note: only a member of this blog may post a comment.