How to Line up Days of the Year in Tableau
As anyone who has ever attempted to complete a #WorkoutWednesday knows, these exercises can be quite a challenge. However, the challenge looks different when you break it down into a series of “Simple Steps”.
Completing the full challenge isn’t required to learn a new skill. Just put one foot in front of the other, and keep learning. That’s progress!
This “Simple Step” sheds light on the x-axis used for the line chart below.
So what is the objective?
First, we need an x-axis similar to the image above, highlighted in blue. The axis values range from 1 to 350 and above. That’s all we can infer from the visual
This poses the question “what sort of values go from 1 to 350 and above?”
There are 365 days in a standard year, so this seems to fit our needs. Let try it out and create an x-axis by selecting the "Day" date part from the drop down.
Interesting... selecting "Day" gets me the wrong sort of day 🤔 We need the days of the year, not days of the month.
Thankfully, I know a little trick that will fix us right up.
In case you missed that calculation, here it is again:
To get the right numbers on the x-axis we need the ‘dayofyear’ date part. This date part isn’t a secret, but could certainly be easier to find. Later, I’ll share a full list of the date parts and link to Tableau date functions.
With the x-axis nailed we can continue to reconstruct the visual.
What is "time" in Tableau's mind?
When working with time, we are working with a dimension that can be split up into many parts. The objective in this #WorkoutWednesday is to “display the running total across all days of the year”. Of course, we had to frame that objective ourselves, but in doing so exposed the date part needed to solve this problem.
We know continuous pills create axes and we know the axis needs to be Day of Year calculation. This means we need to place that continuous pill on the columns shelf. To separate our data by their respective year, the year date part needs to be placed on the Detail shelf of the Marks Card.
Many more steps are needed to solve the full #WorkoutWednesday, but this should get you one step closer.
Taking this concept one step further
In Tableau - and other computer application - dates are just numbers. Heck, everything is just a number to computers. Take a second to chew on that thought... Mmm 😋
Date parts differ from date values - two ways to express temporal numbers in Tableau - in that each date part has a different number line.
Date values have a number line that starts on midnight January 1st, 1900 and extends to now. On the other hand date parts' number line fully depends on whether you are referring to the years, quarters, months, or some other way we group time.
For example, quarters will only ever extend from 1 to 4. Months from 1 to 12, Weeks from 1 to 52.
In order to create these individual number of lines, you’ll want to take advantage of the DATEPART function in Tableau. This function outputs a different range of whole numbers (integers) depending on the first argument you pass in.
Thinking of dates isn't quite as much of a stretch as thinking of your best friend in terms of the individual atoms they're made of, but it's a stretch none the less. I wouldn't be surprised if we talk about how the lowest common denominator in many of the challenges are numbers and solving problems with Tableau is a test of your ability to manipulate those numbers accurately.
I hope this Simple Step has been fun and thought-provoking. Go out there and see what you can do with your dates. How can you line them up? How can offset them in strange ways? How can you ask more interesting questions of your data?
If you have interesting questions for me, I love hearing them. Simply drop me a line at firstname.lastname@example.org. Keep it simple, Sexy 💃