Tableau Deep Dive: Dates – Custom Dates

Data

Tableau Deep Dive: Dates – Custom Dates

Tableau Deep Dives are a loose collection of mini-series designed to give you an in-depth look into various features of Tableau Software.

Let’s continue on with our Deep Dive into Dates. This article, we’ll explore custom dates. The name admittedly is a bit of a misnomer and only really make sense if you understand date parts and date values. Make sure to catch up on this series by starting with Part 1. Let’s get after it.

Choosing the Date Value / Part

When I drag a date field onto my view, I have the option of choosing discrete (date part) or continuous (date value). Next to each there will be a plus sign to allow my user within my view to either expand my date part hierarchy or to change my date value.

That’s great … unless I don’t want my user to be able to change the level of detail of my date field. That’s the benefit of custom dates.

To create a custom date, right-click on your date field and select Create > Custom Date:

Create > Custom date” src=”/wp-content/uploads/sites/default/files/blog-content/TDDDatesCustom1.png” /></p>
<p>That brings up the Create Custom Date dialogue box. Remember what we learned in article #1 about date values product continuous dates and date parts producing discrete dates by default? The two radio button selections refer to that.</p>
<p align=Create Custom Date

Notice that Date Part is greyed out. It’s because we start with exact date in the detail drop down. Exact date by default is going to be continuous. Refer to our previous article (#4), how we can hardcode an exact date to be discrete and how that can help us.

We are going to be creating a calculated field. There are three important elements to remember when you name your field, 1) the original date field, 2) the level of detail and 3) whether its discrete or continuous. Let’s make select the detail of year and select date value.

Order Date (Continuous Years)

After clicking OK, you’ll find your new field in the Dimensions section of the date pane:

Dimensions

The data type is now green. This is the biggest exception about dimensions typically being discrete. As you can see now, there are dimensions that are continuous. Notice also that the data type icon has an equals sign on the left of it, indicating that this is a calculated field.

Let’s bring this into our view and see how our custom date for continuous years looks:

Bring into view

There is no plus sign to change the level of detail next to our custom date. It’d be the exact same result for a custom date using discrete year.

Custom Date Hierarchies

We can use our custom dates to create custom hierarchies with our dates. A standard date field will progress as follows: Year > Quarter > Month > Day. We can use custom dates to recreate this hierarchy however we want. Let’s get crazy. I want my hierarchy to go Year > Month > Quarter.

Custom date hierarchy

Our hierarchy of custom date parts would expand exactly in that order, but that’s admittedly just silly and not super useful.

Let’s change gears and create a custom hierarchy that might be more helpful by including a parameter that allows our user to change whether they are using a hierarchy with calendar year or switching that custom date to a fiscal year. Let’s get our custom dates prepared. Let’s create a discrete year, month and day on Order Date:

Order Date (Discrete Years)

Order Date (Discrete Months)

Order Date(Discrete Days)

Now we need our financial year custom date. Right-click on Order Date and select Create > Custom Date. We want the detail of year and the date part radio button selected. Click OK. Now right-click on your new field and go to default properties to change the Fiscal Year start to July. That’s when we start our financial year down here in Australia.

Fiscal Year view

We need a parameter to let our user decide if they want to see our date by calendar year or fiscal year. If you need a refresher on how to use parameters, check out this Deep Dive.

Create Parameter

Now we’ll create a calculated field that will serve as a turnstile to serve up whichever custom date field the user wants to see:

Calculated field

Your calculation will be a measure, but we want it to be a dimension so drag it up to the dimensions section of the data pane. Drag our custom date field for months onto the Year calculated field in the data pane to create a hierarchy:

New hierarchy

Let’s prepare our view. Drag the new hierarchy onto Rows and Sales onto Columns. Right-click on the parameter to show the parameter control. Clear from the title and insert our parameter so that it dynamically update with the user’s selection. The parameter allows us to change between calendar and fiscal year in our hierarchy.

Choose Fiscal Year and expand the hierarchy down to months. You’ll see we have one more challenge when it comes to sorting our results:

View results

Months above are being sorted as if they were in a calendar year. For our Australian fiscal year, July should be first. We need another calculation that will order our months correctly to swap out the field for a FY field just like we did for year:

Month calculation

We’re assigning a value of 1 to July and on up for the fiscal year selection, continuing with 7 for January. You can see that we didn’t actually need our custom date field for months, but I wanted you to see the process that it took for us to get here.

Now our months will sort correctly:

Months sorted correctly

We’re continually going back to those foundational date functions that we explored in Part 2 and Part 3 of the Deep Dive on Dates. We’re capable of doing a lot of cool stuff with dates as a result.

The Tableau Packaged Workbook that I created in the example above can be downloaded below.

What’s Next?

Are we already on article #6? Wow, dates are fun! In our next installment of this Deep Dive, we’ll build the calculation for a rolling date. For instance, how many orders did our customers make twelve months after their first order? Until then, be sure to leave any thoughts or questions in the comments section below. Cheers!

Want More Tableau Deep Dives

  1. Tableau Deep Dive: LOD – Introduction to Detail
  2. Tableau Deep Dive: LOD – The Include Calculation
  3. Tableau Deep Dive: LOD – The Exclude Calculation
  4. Tableau Deep Dive: LOD – The Fixed Calculation
  5. Tableau Deep Dive: LOD – LOD Calculations vs. Table Calculations
  6. Tableau Deep Dive: Parameters – Parameter Overview
  7. Tableau Deep Dive: Parameters – Parameter Properties
  8. Tableau Deep Dive: Parameters – Filtering – Top N
  9. Tableau Deep Dive: Parameters – Calculated Fields
  10. Tableau Deep Dive: Parameters – Filtering Across Data Sources
  11. Tableau Deep Dive: Parameters – Bins
  12. Tableau Deep Dive: Parameters – Reference Lines
  13. Tableau Deep Dive: Parameters – Table Calculations
  14. Tableau Deep Dive: Sets – Introduction to Sets
  15. Tableau Deep Dive: Sets – Constant Sets
  16. Tableau Deep Dive: Sets – Computed Sets
  17. Tableau Deep Dive: Sets – IN/OUT
  18. Tableau Deep Dive: Sets – Combined Sets
  19. Tableau Deep Dive: Sets – Calculated Fields
  20. Tableau Deep Dive: Sets – Hierarchies
  21. Tableau Deep Dive: Dates – Introduction to Dates
  22. Tableau Deep Dive: Dates – Preparing Dates
  23. Tableau Deep Dive: Dates – More Date Functions
  24. Tableau Deep Dive: Dates – Exact Dates
  25. Tableau Deep Dive: Dates – Custom Dates
  26. Tableau Deep Dive: Dates – Rolling Dates
  27. Tableau Deep Dive: Dates – Calendar Filters
  28. Tableau Deep Dive: Dates – Week-by-Week Comparison
  29. Tableau Deep Dive: Dashboard Design – Planning
  30. Tableau Deep Dive: Dashboard Design – Layout & Structure
  31. Tableau Deep Dive: Dashboard Design – Proof of Concept
  32. Tableau Deep Dive: Dashboard Design – Adding Interactivity
  33. Tableau Deep Dive: Dashboard Design – Visual Best Practices
  34. Tableau Deep Dive: Dashboard Design – Optimization & Governance
  35. Tableau Deep Dive: Dashboard Design – Publishing
  36. Tableau Deep Dive: Table Calculations – Custom Sorts, Part One
  37. Tableau Deep Dive: Table Calculations – Custom Sorts, Part Two
  38. Tableau Deep Dive: Table Calculations – Custom Sorts, Part Three

More About the Author

Robert Curtis

Managing Director, APAC
Building Solutions with InterWorks at Corinium’s Data Architecture Conference in Melbourne InterWorks was a proud sponsor of the Data Architecture Conference hosted by Corinium in Melbourne on 21 and 22 June 2023. Hundreds of ...
Virtual Event Recap: Deep Dive of Tableau Parameters For our June Lunch and Learn, we did a deep dive on parameters for our friends across Australia and Singapore. Tableau is the premier ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072

×

Love our blog? You should see our emails. Sign up for our newsletter!