#workoutwednesday | Data Visualization | Tableau

Workout Wednesday 11 – Small Multiple grids

This week’s #workoutwednesday was about creating small multiple grids to use the char() function extended to use unicode characters.

Requirements

  • Create 1 sheet with a Small Multiple of 20X5 Grids for each Sub-Category in Office Supplies
  • The percent of Total segment starts from the Bottom Right.
  • The Sub-Category name needs to be above the Grid, with the Percent of Total
  • Dashboard is 800X800
  • YOU CAN ONLY USE THE TEXT MARK. NO BLENDING, NO SQUARES, JUST TEXT.

My attempt to create the chart as per requirement:

Here’s the steps I did to re-create the dashboard shared:

Step 1: Import the super store data into Tableau and created 2 calculated field named rows & columns as shown in screen shots (taken from hint given on Andy’s post):

Step 2: Drag row into rows shelf and columns in column shelf then apply context filter on “Office supplies” categories.

Step 3: Drag sub-categories to text label in Marks area and sort the data by sales then for both rows & columns compute using –> sub-category. Next is to perform the calculation into discrete mode for both rows & columns

Step 4: Create a calculated field “% of total” using below formula:

Next is to create calculated field “Circle”

Now create calculated field “block” to create line break in field:

Step 5:  Next is to finally create last 2 calculated field named Right and Left to show circles on the chart as required:

Left:

Right:

Step 6: Drag % of total, left and right into Marks shelf and adjust the screen view to entire view:

Now, format the Label text as below:

 

Hope you enjoyed the post 🙂

Thank you!

Do subscribe to blog for new interesting posts

Data Visualization | R | Stats | Tableau

How to use Tableau with R

Today’s blog we will learn about running R codes with Tableau. R is the most used statistical tools by data scientests and researchers across the globe. Together, R & Tableau could be extremely useful in data science arena as it can help organisations to unravel end to end discovery needs.

Here in the blog we will go step by step process of integrating R & Tableau

Prerequisite: R studio & Tableau already installed on machine

Step 1

Install Rserve Package on your R software

install.packages("Rserve", repos="https://cran.rstudio.com/bin/windows/contrib/3.4/Rserve_1.7-3.zip")

## Error in install.packages : Updating loaded packages

Once the package is installed, we will run below command to load the package

library(Rserve)
Rserve()

## Starting Rserve...
##  "C:\Users\HP-LAP~1\DOCUME~1\R\WIN-LI~1\3.4\Rserve\libs\x64\Rserve.exe"

This will start the server in the background to enable Tableau to execute commands on R

Step 2

Now we will connect Tableau to R server using following

Help –> Setting and Performance –> Manage external Service connection

This will open the below shown window with auto populated values for Server as “Localhost” and port as “6311”.

Click on Test connection to check the connection, it will show successfully connected to Rserve in pop-up

click on ok
This will help Tableau to execute script commands like:
Script_BOOL
Script_INT
Script_STR
Script_REAL

Step 3

We will take below FMCG example as our data source and import into Tableau

Step 4

Now, we will create an calculated field “Outliers” using following code to find outliers in the FMCG data:

IF SCRIPT_REAL(“library(pracma); a <- rep(1, length(.arg1)); a[findpeaks(.arg1,threshold=quantile(.arg1,.99),sortstr=FALSE)[,2]]=0;a;”, SUM([Sales])) == 0
THEN “Outlier”
ELSE “OK”
END

One more calculated field “Cluster” with following code

SCRIPT_INT(
‘set.seed(42);result <- kmeans(data.frame(.arg1,.arg2,.arg3,.arg4), .arg5[1]);result$cluster;’,
max([Sales]), SUM([Profit]),min([Freight Expenses]),MIN([Discount offered]),3)

Step 5

Drag order date on column shelf and sales on row shelf then drag “Outliers” into color

As you can see all the outlier values are highlighted in orange while other values are in blue color. This way we can find outliers in our data very easily by using dream team of Tableau and R

Step 6

Lets go one step further and see if we can do clustering in Tableau with R.

Drag Sales into column shelf and profit into row shelf (unselect analysis –> aggregate measure) then drag calculated field “Cluster” to color in marks area and add trendline in the chart. We will see it creates 3 clusters as shown below:

Hope this blog helps to understand how to run Rserver in background and execute R scripts using Tableau.

you can download tableau file from here

Thanks for reading the blog

Do subscribe to my blog and keep receiving new posts 🙂

Data Visualization | Tableau

Pareto Chart in Tableau

“A Pareto chart, named after Vilfredo Pareto, is a type of chart that contains both bars and a line graph, where individual values are represented in descending order by bars, and the cumulative total is represented by the line.” – Wikipedia

General assumption in Pareto chart is that 80% of the output is generated by 20% of input. This ratio is not accurate for all cases but it is very much accurate for most.

Lets try making one using #makeovermonday data on http://www.makeovermonday.co.uk/data/ for week 8 i.e. where does your medicine come from?

Step 1: Import the excel file into Tableau, Filter the data with year = 2016 and put a filter to have all countries expect world for exporter field. Then, drag the Exporter field to columns shelf and exports (USD) into rows shelf in worksheet and sort the exporter field in descending order by Exports (USD)

Step 2: Click on sum of Exports (USD) and select quick table calculation –> running total to calculate the running total of the values

Now our Pareto chart is ready but still we haven’t figure out the 80:20 ratios on output to input:

Step 3: Now we have to change the Exports(USD) axis from running total to percentage of total. To do this, please right click on the Exports(USD) on row shelf and select edit table calculation option as shown below:

 

Step 4: Once edit table calculation opens, please perform following:

  • Change compute using from Table across to Specific Dimensions –> select Exporter field
  • Select the Add Secondary calculation option given and select the secondary calculation type as Percent of Total
  • Also, change the compute using from Table across to Specific Dimensions –> Exporter Field

 

Now in below image we can Sweden is in top 12 but Exports(USD) is already at 82%

Step 5: Right click on Y-axis and Add a reference line then set Line value at 0.8 (i.e. 80%)

Step 6: Now create a calculated field to get Exporters percentage using Index() and Size() function:

Step 7: Drag the new calculated field to column shelf and drag exporters column to details under Marks then select the chart type as Line graph and also change the axis format to % as shown below

Step 8: Add a reference line on X-Axis and set Line Value at 0.2 (i.e. 20%) as shown below

From below screenshot, we have successfully create Pareto chart in Tableau:

Tableau workbook can be downloaded from here

Thank you visiting my blog 🙂

Do subscribe to keep receiving updates and new learnings

#workoutwednesday | Data Visualization | Tableau

#WorkoutWednesday Week 10 – KEEP AN EYE ON SALES (Radial Chart)

This week’s #workoutwednesday was about creating radial chart with lines equally spaced on the percent of the year completed.

Requirements

  • Use sales for 2017.
  • The size of the dashboard 500px x 600px.
  • If you think of the segments as a clock, Week 1 should be at the 12:00 position.
  • The weeks should be evenly spaced based on the week number.
  • You’ll be using geometry. The minimum radius is 1. The maximum radius is 2.3. (Spoilers provide some math help.)
  • The space between the segments is .15.
  • Note the tooltips to get some assistance on your math.

Here is my radial chart using LOD functions and bit of trigonometry functions to generate chart:

Thanks 🙂

Do subscribe to blog for keep receiving updates

#MakeoverMonday | Data Visualization | Tableau

#MakeoverMonday: Equal Measures 2030 (International Womens Day)

#Makeovermonday partnered with Equal Measures 2030 for week 10 as we approach International Women’s day. This week we will be seeing how good or bad policymakers for 5 different countries are and how much awareness they have with women welfare in the country.

Here is the original dashboard:

I started with playing around with the survey data and create dashboard in simplified way to understand the responses and how well policymakers know the state of women in their respective countries. The requirement and data was share on http://www.makeovermonday.co.uk/data/ for week 10.

Below is my dashboard for #makeovermonday:

Thanks!!
Do subscribe to keep receiving regular updates