Hi everyone, welcome back to my blog. This week’s post will be about creating charts and graphs using tableau. We have finished our first month of training at the data school. For last week’s challenge, we were asked to pick 5 challenges from WorkoutWednesday and present How did we solve these challenges. The task seemed to be easy at the first glance. However, It was not that simple. The formatting from the challenges was very detailed and required us to spend a decent amount of time to match the solutions from the authors. Most of the challenges I picked were from Luke Stanke. I also followed him on Tableau Public as I like his works a lot.

The tutorial that I would like to share with you today is the unit chart from challenge week 15 – 2018. I think it is the simplest challenge that I picked from the list of five. It took me around 30 minutes to put the chart together excluding final formatting As I’ve already known how to build a Waffle chart. I will walk you through my solution for this challenge with just 4 TABLE CALCULATIONS.

Link to the challenge: April 10, 2018 – Workout Wednesday (workout-wednesday.com)

This is how the solution looks like

First of all, let’s just go through the challenge’s checklist of requirements. The challenge requires you to create the list of the top 5 sub-categories with the most products, and every dot represents one product. Below is the plan on how to build this

  • Create a set of top 5 Sub-Categories
  • The level of detail needs to be on the product name
  • Create the number of rows and columns for the dots
  • Build the calculation that categorises Top 1st 100, 2nd 100, and 3rd 100 
  • A final touch of formatting including: create gaps between groups and colour coding them.

We’ve got the plan. Let’s go ahead building this together.

1. Building the chart
  • Creating a top 5 Sub-Categories with the most #products

#products

{ FIXED [Sub-Category]:

COUNTD([Product Name])}

Right-click on Sub-Category – Create – Set…

  • Creating rows and columns

To build the number of rows and columns we need 3 formulas using table calculations

Index

Index() – 1

# start from 0 and every single sub-category will start with a product on the bottom left with index 0

Columns

INDEX() % 10

//Returning the remainder of the division

//10 is the number of columns that the challenge requires (you could turn this step into dynamic by creating a parameter on the number of columns.       

Rows

INT((INDEX() / 10))

// Returning the integer (quotient) only when dividing index by 10

Now drag  [Product Name] into the Mark panel. Put Top Sub-Category Set into the filter. Then drag [Sub-Category] into Columns. It’s time to use our calculated fields by putting [Rows] on Rows and [Columns] on Columns then right-click – compute using Product Name.

  • Group the products into 3 different groups: 1st 100, 2nd 100 and 3rd 100 

In order to do so, we need to create a calculated field as below

[Product Group]

if [Rows] <= 9 then ‘1st 100’

ELSEIF [Rows] <= 19 then ‘2nd 100’

else ‘3rd 100’

end

Now drag [Product Group] into Rows

At this stage, we got the unit chart that we need. The final step is just formatting

2. Formating 
  • Create gaps between the 3 different groups

Right-click on the 3rd 100 Group’s Y Axis – Edit Axis, tick the third option, and make sure to leave the Include zero box empty. Repeat the same process with the other groups. Finally, we just need to hide these Axes by unticking Show Header.

  • Labeling and colouring 

To label the number of Products, drag [#products] on Rows and change it to Discrete. To colour the Product Group, put [Product Group] on colour and compute using [Product Name].

We’ve finished building our chart and the last step is just putting it on the dashboard and creating a title matching the solution. I hope you enjoy the tutorial. I will see you next week.

 

The Data School
Author: The Data School