Have you ever tried to apply a calculated field and it didn’t work in the way you wanted - or at all?
As a self taught Tableau Public user I often encountered this issue - especially when working with BIG datasets and I never understood why.
I often found myself creating FIXED LOD calculations trying to alleviate the issue but some cases would require an absolute mess of a nested, nested, nest- (you get the idea) LOD calculated field. Surely it could not be this complicated to isolate a small subset of records? There is no way analysts spend hours manually calculating and entering records on a fresh excel sheet to later join on Tableau, just to have the data to visualise right? Well…
Welcome to the wonderful world of Data Cleaning and Preparation!
My first week at TIL: The Foundations of Data & Tableau Prep
The week kicked off by learning the fundamentals of data: what it is, what an ideal data structure consists of, then moving on to more complex concepts like ways of cleaning data, granularity and best practices.
Though it may not sound like revolutionary wisdom, learning “simple” concepts such as: fields being one data type throughout; spelling inconsistencies causing errors; mixed datatypes e.g. “75%” being read as a string rather than an integer/float or the data structure itself being poorly formatted (think of individual dates being in separate columns each) made a world of difference in understanding why filters and calculated fields did not apply as expected.
This cornerstone of knowledge would have saved me a lot of time and frustration in the past when working on dashboards.
Tableau Prep is a powerful tool in helping alleviate the problems that come with "dirty" data; above shows the journey of 5 excel sheets becoming 1 clean dataset with additional aggregations/added information depending on the desired output.
Cleaning data could be as simple as correcting spelling mistakes to complex structural changes like pivoting, aggregating and filtering. Although Tableau Prep is not readily available like Tableau Public - not to mention beyond the scope of what is expected of aspiring analysts - there are key lessons to take away from this brief insight.
Tips for aspiring analysts - regarding data:
1) PLAN! PLAN! PLAN!!! - By planning your work out you will get a better idea of the direction you want to head in, your progression and improving your understanding of the data you're working with.
2) Know Your Data - Research the subject/background, understand what fields are what data type, check for spelling and data inconsistencies! Tableau usually does a great job at setting data types so if a field is different to what you were expecting, look into why this occurred! Also consider what you want to know and what you're able to answer with your dataset. Would you need another dataset to join? Could you ask a different question/ find a different angle? Does the level of detail allow for this to be answered?
3) Clean your data with the resources at hand - Excel, for example, can pivot/transpose columns & rows which would be useful in merging individual date columns together; if joining sheets is problematic check for spelling/syntax errors in the fields; Filter out data you don't need, ensure each field is one data type throughout. It may not be the most sophisticated method but this could save you a lot of time and energy.
4) Be patient and don't give up! - Creating a dashboard can be stressful and tedious... Understand, at this stage, there are many things you won't know or know how to tackle - And that's OK! You aren't meant to be an expert so enjoy learning as you go along and don't be afraid to make mistakes.
Hopefully the nuggets of information in this blog help make a difference in understanding why your actions don't produce the expected outcome in Tableau.