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!
Recently, I received an email from a blog follower wondering if a single field in Tableau could categorize data at multiple date unit levels (e.g. year, quarter and month). With a bit of calculation creativity, we were able to write a calculation which…
Summarized data at a yearly level if it was from last year.
Summarized data at a quarterly level if it was from before this quarter.
Summarized data at a monthly level if it was from this quarter.
Have a similar need? Check out the video below to learn how we approached this calculation!
Here’s the code (and annotation) behind the calculation in the video.
IF YEAR([Order Date]) < YEAR(TODAY())-1 THEN "Exclude" //Filter on this value to remove values from 2 years ago or earlier
ELSEIF YEAR([Order Date]) = YEAR(TODAY())-1 THEN STR(YEAR(TODAY())-1) //Returns label for last year
ELSEIF DATEDIFF('quarter',[Order Date],TODAY()) > 0 THEN STR(YEAR(TODAY())) +" Q" + STR(DATEPART('quarter',[Order Date])) //Returns quarter labels for quarters this year (but not the current quarter)
ELSE STR(YEAR(TODAY())) + " " + DATENAME('month',[Order Date]) //Returns month labels for months in current quarter
END
Want to learn more? Check out our upcoming classes, webinars and office hours on Eventbrite!