Workout Wednesday: Continuous Dates are Tricky

Emma sure can be devious! Don't let that smile of hers trick you. Today her challenge was to create this chart:


Her requirements are pretty straightforward. I was able to get everything quickly with the exception of the month labels on the x-axis.

FIRST ATTEMPT

To do the line chart, I created a Day of Year calc and plotted that on the x-axis. Notice this results in the day number on the scale.

SECOND ATTEMPT

To format the scale, I first changed the number format to mmm to give me month abbreviations. That gets me close, but some months are missing and the months are labeled at the middle of the month whereas Emma's are labeled at the start of the month.

THIRD ATTEMPT

I sent Emma a message with a few questions, basically because I was stuck. All she said was:

You'll have to re-think the date you have on the x-axis so you can also colour by year.

What does that even mean? I'm beginning to get a sense for the sort of torture I put people through. Ok, so I somehow need to get my x-axis to act like a date, yet still be able to show every day for each year in the view. I also need it to only be month and day. Hmmm: Hey Google, can you help?

Yes indeed! I searched for "tableau month and day of year" and the second search result took me to the Forums which had exactly the question I was asking. The brilliant Jonathan Drummey came up with this formula:

DATE(DATEADD('day',DATEPART('day',[Date]),DATEADD('month',DATEPART('month',[Date])-1,#1903-12-31#)))

Sweet! All I needed to do was swap out [Date] for [Order Date] and I was good to go. Now I have the exact result I needed.

Awesome challenge! I love learning something new! Here's my final product.