Data Viz Done Right

August 9, 2017

Workout Wednesday: Continuous Dates are Tricky

4 comments
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.

4 comments :

  1. Below calculated field does work as well.
    DATEPARSE("dd-MMMM-yyyy",DATENAME('day',[Order Date])+"-"+DATENAME('month',[Order Date])
    +"-"+"1998")

    ReplyDelete
  2. Hi Andy,
    this makes for simpler calculation -- MAKEDATE(2004, MONTH([Order Date]),DAY([Order Date]))

    ReplyDelete
  3. Dear Andy,

    Thank you for your sharing!

    I came up with similar situation before and turned out i used the following

    MAKEDATE(2000,MONTH([Order Date]),DAY([Order Date]))

    The year 2000 is just being selected randomly.

    I would like to know if there is any difference between your method and mine.

    Thank you.

    Pong

    ReplyDelete
    Replies
    1. There is always more than one way to solve something in Tableau. Nice technique!

      Delete