By: Eric Parker
Eric Parker lives in Seattle and has been teaching Tableau and Alteryx since 2014. He's helped thousands of students solve their most pressing problems. If you have a question, feel free to reach out to him directly via email. You can also sign up for a Tableau Office Hour to work with him directly!
In the past, we’ve covered how to write a calculation which allows you to compare today to the same day of the year in Tableau. In that scenario, we used the day of year number for comparison. That works well if, for instance, January 27th from this year should be compared to January 27th of last year. However, what if you want to compare so that today is being compared to the equivalent weekday from last year?
For example, maybe it’s important to know how the 4th Friday in January this year compares to the 4th Friday in January last year. If that’s the case, you aren’t trying to compare to the SAME day as last year but rather the EQUIVALENT day. Check out the video below to learn how you can use Tableau table calculations to perform this form of comparison!
Select here to visit example Tableau Workbook.
Want to learn more? Check out our upcoming classes, webinars and office hours on Eventbrite!
Steps written out, ordered and with calculations included:
1. ["Current" Sales Year to Analyze] Parameter or calculation to choose
2. [Same Day] calculation:
DATE(IIF(YEAR([Date])=["Current" Sales Year to Analyze],[Date],[Date]+364))
3. [Relative Date] calculation:
DATE( IF YEAR([Date]) = ["Current" Sales Year to Analyze]
THEN [Date]
ELSE DATEADD('year',-1,[Same Day])
END)
4. [Current and Comparison Year Only] Create calculation to keep only current and comparison year
YEAR([Same Day]) = ["Current" Sales Year to Analyze]
5. [Only Show Current Year] Create calculated field with table calculation to filter out previous year
LOOKUP(MIN(DATEPART('year', [Relative Date]))) = ["Current" Sales Year to Analyze]
6. [Sales Growth Percent] Create calculated field which returns % growth from comparison year to current year:
(SUM([Sales]) - LOOKUP(SUM([Sales]),-1))
/
LOOKUP(SUM([Sales]),-1)
6. Assemble worksheet (follow video for instructions)
(Optional)
7. [Current Year Sales] create calculation for current year sales and add to tooltip:
SUM([Sales])
8. [Comparison Year Sales] create calculation for comparison year sales and add to tooltip:
LOOKUP(SUM([Sales]),-1)