Tableau Tip: Group Years into Decades Using Calculated Fields

In a recent Tableau project, I wanted to divide a long span of years into decades, as this would provide a more visually effective way to grasp the growth of revenue from top movies (data from The Movie Database) over time. With a little searching, I found the pieces I needed. Below I’ll include a description of my process, followed by links to the helpful sources of insight I found on this topic.

First, here is the visualization with total revenues year by year. Notice that despite its current width you still have to scroll left to reach the early 1900s. Meanwhile, the difference year to year is not in itself that interesting.

Top TMDB movie revenue totals by year a partial view

Now here is the visualization when years are chunked into decades. Much more effective!

Top TMDB movies total revenue by decade

DISCLAIMER: These charts use revenue numbers as entered in The Movie Database by contributors based on publicly reported figures. Thus, the data includes only a portion of all movies. I’ve as yet made no adjustments for inflation.

Getting to Decades from Dates

Now for the process of getting decades from dates. I broke my approach into two steps:

  1. I first created a calculated field to pull the Year from the Release Dates field, using Tableau’s DATEPART function.

    Calculated field to get only the Year from the date information in Release Date

    Once that field was created, I moved the new calculated field from Measures to Dimensions, where it should be.

  2. Then I created a Decades dimension as an additional calculated field. This calculation uses Year and the modulo operator to round each year down to the nearest multiple of 10.

    Calculated field to round each year down to its decade using modulo

    Then, similarly, once created, I made this calculated field a Dimension.
    That’s all it took!

Many thanks to Nick Parsons and Erik Bokobza for their helpful replies in the Tableau Community Forums. Links below.

Recommended Reads

Why does Excel keep mangling my date formats! When my date range spans multiple centuries …

When working with a date range that spans multiple centuries (for instance, late 1800s to present), it’s important to know a few things before viewing or saving the data in Excel. (I’m currently working with Excel 365 for Mac and Excel 2016 for Windows.)

Suppose you’re working with data stored in a CSV file and want to examine it in Excel. Here is a short list of things to watch out for:

  1. Excel for Mac automatically formats dates in m/d/yy format, shortening years to two digits in the process. (Thus 1915-02-08 becomes 2/8/15!) If you then save back to CSV, it will overwrite four-digit years to two, thereby ruining your date fields — as there will be no record of which century it’s from. You’ll need to go back and recover four-digit years from your source. This is bad.
  2. Excel for Windows defaults to m/d/yyyy format. This is not so bad, as the full four-digit year values are maintained.
  3. Neither Excel for Mac or Windows recognizes dates before 1900, instead treating such dates as text. (Thus 1898-01-01 remains ‘1898-01-01’, as text.) On the plus side, it does not change the formatting of these dates.
  4. For the above reasons, if you view date fields in Excel for Mac or Windows, it makes good sense to immediately format your dates to yyyy-mm-dd (following the international standard for data formats: ISO 8601). This requires using custom formatting in Excel. But it’s effective and can save your bacon. (Plus, it jibes with Python pandas and R.)

To reformat dates in ISO 8601 format in Excel for Windows:

  • Go to Format Cells and select the Number tab.
  • Then use the Custom category, and type in the formula: yyyy-mm-dd

Reformat dates to ISO 8601 yyyy-mm-dd in Excel for Windows

In Excel for Mac, the process is similar, but the option we need is (currently) available under the Date category:

  • Go to Format Cells and select the Number tab.
  • Then use the Date category, and select the option starting with a four-digit year, followed by a two-digit month and two-digit day, with hyphen separators. (Excel for Mac currently displays this with the sample date: 2012-03-14.)
  • Alternatively, do as in Windows Excel, and enter it as your own Custom format: yyyy-mm-dd.

Reformat dates to ISO 8601 yyyy-mm-dd in Excel for Mac

 

For Further Reading

The data revolution is now transforming the world of finance

This article from Tech Republic is worth a read. In summary: The data revolution is now transforming the world of finance. A recent Deloitte survey reveals that traditional roles are being automated. To be a human working in finance, you need skills in data science, analytics, and visualization. More than manipulating spreadsheets, you need to create business value with data-informed innovations.

The finance robots are coming — TechRepublic.com