Today’s task used the UN Air Quality Data, but we were forbidden from using Alteryx. This was our go-to data cleansing and preparation tool. This meant that we had to either use Tableau Prep or figure out a way within Tableau to get it on the grain we needed it to be.


Fixed LOD Friday:


Data Problems

There were several datasets given to us separated by the year. But since all of these were in the same structure, it was easy just to union within Tableau itself. However, the grain of the data was more of an issue.

Idealistically for each day, country and city we should have the measure value. However the grain also includes the species. Here the species is the type of pollutant or important air quality index they are measuring. To solve this I had two choices: either to pivot the species that I wanted or to use a fixed LOD to just get whatever species I wanted.

As the heading suggests, I used a Fixed LOD. This is mainly for two reasons: the first is that I didn’t need to open up prep and I could just do it in Tableau. The second is that at the time, I wasn’t really sure exactly what indexes I wanted to further investigate and I would have had to pivot each species individually in Tableau Prep and so that would have taken a a long time.

For an LOD, you do have to be careful on when you are showing different granularities of data. I eventually decided on two measures, PM 2.5 and temperature. Which I needed to have 3 separate LODs for each measure.

In each case, you can make sure to show the correct granularity.

Another problem is that the data contains different amounts of records and sometimes different date ranges, e.g. 2020 at this point hasn’t ended yet.

Thus, I created a bubble chart to show the different amount of records. It also acts as a filter to select the different year.



The first thing I wanted to have is a drilldown from country into cities. There are 3 ways that I considered to do this by: set action, filter action or parameter action.

Set Action:

This method uses only one sheet to show a city and so the user can still see the other countries and easily select another country. The downside to this is that it lacks customisation. I wanted the city to show two things at once. Whether it was also higher or lower than the safety threshold and how it compares to other cities in the same country.

Filter Action:

Filter action is probably the simplest method. Just filter the sheet and then on clear choose exclude all values. This allows you to hide the sheet when nothing is selected. However, this meant that it still showed the country level above it. Which I wanted the focus just to be on one sheet. Thus, I settled on the only option left.

Parameter Acton:

To do this I needed to have a parameter with all the cities and an all value. Then a calculated field on the filter to show/hide sheets depending on whether ‘all’ or a city selected. Lastly another button that allowws you to click on it and change parameter back to all and back to country level view.

Final Visualisation:


Reflection of the Week

This week has been a wild ride! It was definitely stressful but also fun experimenting with new charts and ideas.


The one day time limit pushed me to work at a higher efficiency than usual. Timeboxing has always been a weakness for me. But I felt like I was better at stopping myself at certain points especially towards the end. This was due to me having a clearer picture of my plan in the beginning. Although I think experience still plays a big role in timeboxing, putting constant time pressure on yourself definitely leads to smarter decisions.


Throughout the week a consistent feedback I have gotten was that – especially for complicated dashboards – there needs to be more context and explanation. Whether it’s inside a tooltip or an info button, I need to clarify and validate some steps. These included: how I did certain calculations, how to use certain charts and the definitions of variables or calculations.


Everyday had a different theme which also resulted in a wide variation of dashboards and techniques. It affirmed my beliefs that there are always multiple ways to do things. You can collect data using APIs, ARCGIS and Alteryx etc. Clean data using Alteryx, Tableau and Prep etc. Using set action, filter actions, and parameter actions etc. interchangeably. It’s even possible to create visually appealing amps just within the default Tableau Mapbox.

Being able to understand and use each gives you flexibility to adapt to different environment and become a better consultant.


The Data School
Author: The Data School