Home > Tableau > Date Hierarchies in Tableau

Date Hierarchies in Tableau

I recently had a request from someone who wanted to be able to drill into their data both from Year – Month- Day AND Year – Week – Day. The first of these is very simple as it’s more or less a Tableau default hierarchy from any date field, although the default also includes quarter (Year – Quarter – Month – Day is the default).

Adding Week into this adds a significant degree of complexity for the simple reason that weeks generally don’t span months in a neat way – i.e. it’s only on very rare occasions that the first of the month is also the first day of the week and the last day of the month is the last day of the week. Almost always the end of a month and the beginning of the next will be in the same week number – in other words some weeks will span 2 months.

The first challenge is to create the Year – Week – Day drilldown. To do this a hierarchy actually has to be manually built. In a previous post I described how to build a hierarchy. Making use of calculated fields it’s possible to build on the hierarchy concept to build a Year – Week – Day date hierarchy.

To begin calculated fields need to be created to return the year, week and day of the relevant date field. In the example I created I used the Tableau Superstores sample data and used the Order Date as my date field. Order Year is YEAR([Order Date]), Order Week is DATEPART(‘week’,[Order Date]) and Order Day is DAY([Order Date]). Once these are created and converted to dimensions (they default to measures as the result returned is an integer) next build the hierarchy, which we will call Year – Week, and order it correctly, with Order Year first, then Order Week and finish with Order Day.

Drag the hierarchy on to the Columns shelf, the Sales on to the Rows shelf, click the + on the Order Year pill on the columns shelf and you’ll see the hierarchy in action. The + sign highlighted signifies it’s a hierarchy and possible to drill down into. Once the hierarchy is opened up to drill up use the – (minus) sign that appears.

Next step is to do the same, but this time for Year – Month – Day. A new calculated field is required for the Month (MONTH([Order Date])) and the existing Order Year and Order Day fields will need duplicating as a dimension can only be in one hierarchy. Once that’s complete build another hierarchy this time called Year – Month.

To see what happens when combining Year – Month – Week – Day into a hierarchy see the example below.

Next is the complicated part – how to combine these to give the user the flexibility to use either the Week or the Month hierarchy depending on their requirements at the time. In the example above you can see that combining Month and Week in the same hierarchy doesn’t work. Therefore a way to work around this has to be devised. To do this a combination of a parameter and calculated field needs to be used. Create a String parameter and set it up as a List with 2 options for the user to choose, Week or Month. Show this parameter control on the worksheet.

Parameter for selecting month or week

Next a calculated field will be created to use the value returned from the parameter. I called the field WeekOrMonth and the formula is: IIF([Month or Week]= ‘Week’,[Order Week],[Order Month])

Drag this new field into the Year-Week hierarchy into the same position as the Order Week, replacing the Order Week field.

Hierarchy with WeekOrMonth field

Now when using the hierarchy you’ll see when drilling down through the dates that either the week or month is displayed dependent on the selection in the parameter. The final step is to tidy this up a bit. I prefer when the Month is displayed using the name instead of the number and for ease of reading I will prefix all of the week numbers with the word ‘Week’.

To do this edit the WeekOrMonth calculated field and change the formula to be IIF([Month or Week]= ‘Week’,’Week ‘ + STR(DATEPART(‘week’,[Order Date])),DATENAME(‘month’,[Order Date])). The end result is as below:

  1. wendy
    January 10th, 2014 at 16:49 | #1

    This’s great and exactly I’m looking for. however, the dashboard didn’t display at all. I really want mass around with the workbook. if that possible? thanks Wendy

    • awatson
      January 10th, 2014 at 23:17 | #2

      Thanks for pointing out the broken dashboards, sometimes this happens with wordpress and I never managed to work out why. Anyway, should be ok with this post now. Thanks for reading.

  2. Rishi
    April 17th, 2014 at 17:19 | #3

    Hi,

    I really like your idea. However i was trying to include the quarter as well.
    I used the formula in the calc field as

    IIF([MONTH OR WEEK]= ‘week’,’week’ +
    STR(DATEPART(‘week’,[ORDERED_CALENDAR_DATE])),
    DATENAME(‘quarter’,[ORDERED_CALENDAR_DATE]),
    DATENAME(‘month’,[ORDERED_CALENDAR_DATE]))

    It says that the calc is correct but i think datepart only satisfies 2 conditions. So i was wondering if there is something that can be done to include the quarter as well

    Thanks

  3. Rishi
    April 17th, 2014 at 17:37 | #4

    @Rishi

    I got the solution….this would be helpful …

    IF([MONTH OR WEEK]= ‘week’) then ‘week’ + str(DATEPART(‘week’,[ORDERED_CALENDAR_DATE]))
    Elseif([MONTH OR WEEK]= ‘month’) then str(DATENAME(‘month’,[ORDERED_CALENDAR_DATE]))
    ELSEif ([MONTH OR WEEK]= ‘quarter’) then ‘Q’+ str(DATENAME(‘quarter’,[ORDERED_CALENDAR_DATE]))
    else ‘n/a’
    End

  4. Vincenzo
    July 3rd, 2014 at 14:21 | #5

    Hi, the example is useful, but if my custom hierarchy is made of number fields, I can use a bar graph but I cannot draw line graphs, which require a date dimension.
    Any hint on how can I build a date hierarchy? I told my manager that maybe it would be better to map our hierarchy to the real date and then let Tableau handle the built-in dates, but he’s concerned about the fiscal year (that we treat in a very peculiar way…)

  5. Wayne
    September 2nd, 2014 at 07:14 | #7

    Hi there

    Was trying to create non standard time periods in a hierarchy format e.g. Start at year and then drill down into 13 x 4 week periods and finally to week? Any idea how i can do this?

    • awatson
      September 14th, 2014 at 08:33 | #8

      You would have to create custom fields and a custom hierarchy. You will need a year field – YEAR([DATE]) – a 13×4 week fields – i.e. IF DATEPART(‘week’,[DATE]) < 5 THEN 'P1'...ETC...END (or user a smarter math based formula instead) - and a week field. Put these 3 fields into a custom hierarchy and you have your non standard hierarchy.

  6. Trent
    April 28th, 2016 at 17:39 | #9

    Hi,

    I am working on blending two data sets together. When I use my date field from one of the data sets, I get 2015, 2016, and *. Same with quarter, I’ll get Q1, Q2, Q3, Q4, *. Could you help me to figure out why the * is appearing at the end of these?

    Thanks in advance!

    Trent

  1. No trackbacks yet.