6 Replies Latest reply on Jan 5, 2016 2:14 PM by Norbert Maijoor

# How to compare current week's data to last year's same time period

Hello,

I am currently trying to find the best way to calculate this week's data and compare the values to the same period but using last year's data. I would like to get both on the same axis similar to the attached picture titled "sample chart."

I have also attached a sample workbook on what I was trying to achieve.  Any insight is greatly appreciated. =)

-Katrina

• ###### 1. Re: How to compare current week's data to last year's same time period

Goodnight Katrina,

I made here a nearby solution to what you want, but not exactly, see if it helps you.

The steps I did:

1 - replaces the date filter with a filter weeks, since you want to compare weeks in different years.

2 - created a Drink filter Type to display a graph at a time. In this way on your dashboard you can have 3 spreadsheets, or filter to choose

3 - created a "view filter" of the date year, lookup (max (year (date)), a technique known in Tableau world, to filter the data only in the preview, allowing you to search the data of the previous year

4 - I created two calculated fields, PCT95 - Current Year and Last Year, and the Last Year uses the formula Lookup - 1, using the table calculation the year to catch week of the previous year

5 - Finally I could not put the fields last year online, because they break down by day of week, or Location. If we take the graph Location then you will stack the bar, putting on the same bar the Blue Cafe and Green Cafe

With these formulas you can try other forms of display.

See if it helps you

thank you

Fabio Idalgo

fabioidalgo.blogspot.com

• ###### 2. Re: How to compare current week's data to last year's same time period

1) Create a calculated filed by using the following synyax

Name: Current Week Sales

Syntax:

if

(year([Order Date] = year(today()) or year([Order Date] = year(today())-1)

and

(datepart(week', [Order Date]) = datepart('week', today()))

then [Sales])

end

2) Then drag the [Order Date], [Current Week Sales] onto work area.

3) Report will show the sales data for Current, Previous Year cureent Week only, will show empty for rest of the dates.

4) Drag the [Current Week Sales] from Measures window to Filters shelf then click on "All" then click on
Special then click on "Show Non-null Values"

5) Then Right click on [Current Week Sales] on the work area, then click on Add quick Table Calculationm\, then click on "Difference from".

The following link has the similar situation what you have.

Best Regards

Kumar

2 of 2 people found this helpful
• ###### 3. Re: How to compare current week's data to last year's same time period

Hi Katrina!

Thanks for reaching out and sharing your challenge.

Find below my approach in attached workbook, version 9.0

• ###### 4. Re: How to compare current week's data to last year's same time period

Hi Katrina,

You have some really good options so far, I just thought I'd add my two cents:

It looks like your original picture was generated from Excel, first I'd like to mention that Excel and Tableau are not built the same way and you should not try to "do what was done in Excel in Tableau." Instead I would try to focus on the main elements of the problem you are trying to solve.

Now I'm not sure what the line is supposed to represent, I see in the workbook something about a 95% confidence interval but I don't see how it applies. Also this particular dataset is tricky because of the low "volumes" and the lack of variation week on week. Below is what I came up with to try to emulate your picture:

You can check the attached file to see how I built it. I hope the feedback has helped!

• ###### 5. Re: How to compare current week's data to last year's same time period

Hi Norbert,

Thank you for posting the workbook.  I was able to get closer to what I needed to accomplish.

• ###### 6. Re: How to compare current week's data to last year's same time period

Hi Katrina!

Glad I could help. Thanks for the awards. Much appreciated!