Dear Data-Agony Aunt: Missing Headers, Case of Date (Format) Rage

Posted by Sophie Sparkeson May 13, 2016

During #SpringDataCleaning month our resident Data-Agony Aunt will be on-site to answer your data-prep questions.

Data-Agony Aunt

Ms. Data-Agony Aunt

Want some advice on how to clean your data?
Tweet your question to #SpringDataCleaning

Dear Data-Agony Aunt: Where Have My Headers Gone?

Data-Agony Aunt replies: Don’t fret, David. This looks like a case for Tableau’s Data Interpreter!

The Issue

You’ll notice that your data set has a “header line” in it containing (in you case) a second set of headers for all the rows: Years and Sectors of Economic Activity. Lots of data sets have header or footer rows that contain extra information about the data set such as a title or information about when the data set was generated.

Header Row
David’s data with a header row

Tableau needs the first row or a data set to contain the column names and for the actual data to start from the second row downwards. Header rows mess up this structure. Thankfully, it’s an easy fix in Tableau’s Data Interpreter.

The Solution

When you bring your data, you’ll notice that the first row contains a lot of nulls. This is Tableau trying to see the non-existent data in these rows. To tell Tableau that your data set has header rows, and that it should look for data further down, click on the “Turn On” button in the Data Interpreter.

Data Interpreter
Data Interpreter in action

Hey presto! Tableau has tidied up your header rows and is reading your data from where it actually starts (the header rows haven’t disappeared, you can still see the information in them by clicking on the “Review results” button).

Dear Data-Agony Aunt: I’m Experiencing Date (Format) Rage!

Data-Agony Aunt replies: I feel your pain! Dates formats can be tricky. Follow these steps to turn date-format-rage into a blissful time.

The Issue

Currently Tableau doesn’t recognise dates formatted as

  • Month_/Year: MMM-YY(eg Jan-16)
  • FY_Year: YYYY (eg ‘2016)

If your date is formatted like that (as yours is), Tableau will either read it as a number (for example the number 2,016), or a text string (2016) depending what the field format/data type is in Excel.

Does this mean that you’ll have to give up on your analysis? Not at all! You can fix your data-formatting woes in the Data Interpreter.

The Solution

Once you have your data in Tableau, you need to decide if you want to keep your date as a number (numbers fields have “#” above them), or if you want to change it into a text string (text fields have “abc” above them). Why might you want change your date’s data type from a number to a text string? Well, Tableau lets you sum or divide numbers, but it doesn’t really make sense to sum or divide years; half of the year 2016 are the months January through June, not 1,008!

How to Change a Field’s Data Type

Simply click on the little arrow next to the data type symbol and select “String”.

Change Format
Changing data type formats

When you go to your first sheet, drag your year field to dimensions if you want Tableau to treat it as a dimension (for more on the difference between dimensions and measures, see Ryan Sleeper’s post).

Dimension Measure
Drag and drop measure to dimension

What If I Want to Make It Look Like a Date Field?

If your data is formatted MMM-YY(i.e. Jan-16),then you might want to create a new field that is formatted like a date (i.e. 01-Jan-2016). Why might you want to do this? Tableau lets you easily group dates. Tou can only look at years, or at months, or at quarters. This is a pretty handy thing to be able to do.

In the Data Interpreter, create a calculated field

Create a Calculated Field
Create a calculate field

And then type in is formula to make every date formatted with "01" (eg first day of the month) in front of it

DATE(RIGHT([Month-Year], 2) + "-" +
LEFT([Month-Year], 3)
+ "-" + "01")

Create a Calculated Field 2
Create a your new date field

Volia! Date rage solved.

Data prep question?

Tweet it to our Data-Agony Aunt!

Tweet to #SpringDataCleaning

Comments

Dear Data-Agony Aunt: I'm glad I'm not the only one who experiences date rage. I have used the solution you outlined: changing my years (2016) to a string. But this causes some frustration down the road. Tableau won't let me create trend lines (it says it needs a date field) and it seems to limit the types of charts I can use. I have a lot of data sets where the Year is a logical column, but I remain frustrated that Tableau doesn't like the YYYY format.

Add new comment