#WorkoutWednesday Week 50 was surely not as easy as pie but it did feel as rewarding as pies. As you all know by this time, Workout Wednesday is a set of weekly challenges from Rody Zakovich, Luke Stanke, Ann Jackson, Curtis Harris, and a host of guests (Rosario, Donna and Sean) designed to test your knowledge of Tableau and help you kick-start your development. The idea is to replicate the challenge as closely as possible without even seeing the solution. This week’s challenge was set by Luke Stanke and it was about Pie chart with certain twists where you need to sort the states by the selected Sub-Category percentage and color the category as per the mentioned requirement.
When you think you have it, leave a comment with a link to your visualization and post a pic on Twitter for others to enjoy.
2017 WorkoutWednesday Challenge: Link
2018 WorkoutWednesday Challenge: Link
Edward Tufte, a pioneer in the field of data visualization and data design, maintains that pie charts “should never be used,” and Stephen Few calls it “by far the least effective” graph intended to facilitate quantitative communication and no one denies this theory but yet, we support pie charts. Why?
Pie charts are useful for representing a simple proportion of a whole, and can easily be interpreted by expert and novice alike. Even Viz Guru Mr.Few admits that there’s one thing the pie chart does better than any other visualization, and that is convey the part-to-whole relationship. While bar charts do show the hierarchical relationship between values better than pies do, they make the viewer work to picture the whole. I would request you to go through the below link before attempting this week Challenge.
With a plan on how to solve the challenge , follow the requirement in a chronological order and write the Calculation and test it by creating the crosstab.
Next to Parameters, click Create, and in the Create Parameter dialog box, complete the following steps
- In the Create Parameter dialog, do the following, then click OK:
- Name: Sub Category Param
- Right-click in the Data pane and select Create Parameter
Data Type: String
Allowable Values: List
Add from the Field : For value, Select ‘Sub Category’ from the dimension list.
- Right-click on the parameter and select Show Parameter Control.
Create a Row Divider and Column divider by using any of the Below Calculation.(* You can use any one of them)
IIF(INDEX()% [Column Selection]=0,[Column Selection],INDEX()% [Column Selection])
Here, [Column Selection] is parameter with an Integer Data type.
if (INDEX()/[Column Selection])= Int(INDEX()/[Column Selection])
THEN (INDEX()/[Column Selection])
ELSEIF(INDEX()/[Column Selection])<0 THEN Int(INDEX()/[Column Selection])
ELSE Int(INDEX()/[Column Selection]+1) END
The Part of the challenge allows the user to select one of the value from the “Sub-Categories Parameter”. Based on the selection of the user, calculated field has to identify the Category from the Sub-Category selection but we have to be careful because we want to end up with a single output not two output.Let’s see what it means..
Category Selection Based on Parameter
IF [Sub-Category]=[Sub-Category Parameter] THEN [Category] END
If no match is found, the default return expression is used and if there is no default return and no values match, then Null is returned.
There are quite a few routes to get the desired result and I will be using Level of Detail Calculations (LOD calcs) to solve my problem because with LOD’s , it easy to determine an aggregation at any level of detail.
Create a dynamic Calculated dimension that divides the Pie into three parts. This will ensure an easy way to apply Color to our Sub Divided Pies.
- Sub-Category Name
- “Rest of” Category Name
- All Others
IF [Sub-Category]=[Sub-Category Parameter] THEN [Sub-Category]
ELSEIF [Category]=[Category Selection Based on Parameter] THEN “Rest Of “+ [Category]
ELSE “ALL Others”
Next step is to write a Calculated Field that will sort the states by the selected Sub-Category percentage
IF [Category Grouping]=”ALL Others” THEN 3
ELSEIF STARTSWITH ([Category Grouping],”Rest”) THEN 2
The Last part of the challenge is to calculate the sales percentages of the each slice of the Pie. We can achieve this either by using LOD’s or by Table calculation.For this particular post , we will use simple Table Calculation and compute it based on the granularity of the view.
% of Each Slice Category
Let’s start by dragging our Calculated fields onto the worksheet
1. Drag Column Value onto Columns
- Drag State onto the Details Mark.
- Right click on Column Value , select Compute Using and choose State
2. Drag Row Value onto Rows
- Right click on Row Value , select Compute Using and choose State
3. Change the Mark Type to Pie
4. Drag Sum(Sales) onto Angle Mark
5. Drag Category Grouping onto Color Mark
- Right click on Category Grouping , select Sort and choose Sort By as Field , Sort Order as Ascending,Field name as Sorting and Aggregation as Sum.
6. Drag State onto Text Mark and do it exactly as per the below image.
7. As we added category Grouping onto the Color Mark , we need to adjust the Column Value and Row value TC which are available on our Row and Column Shelf.
Right click on Column Value and Row Value , select Compute Using and choose State and Category Grouping.
8. Drag % of Each Slice Category on Tooltip and compute as per the below image.
Once you are done, you should get the following looking worksheet.
9. Create another sheet which shows the Category Grouping Label as per the below image (*use this ASCII Character ▮), add this to the Dashboard and position in the center of the worksheet.
Your Final dashboard will look like the below image and dont forget to post a pic on Twitter for others to enjoy.
If you have any questions please do not hesitate to contact me on twitter (@rajvivan) or LinkedIn (@rajvivan)
Happy Dashboarding and Merry Christmas to everyone! See you all next Year -2019!
Leave a comment
You must be logged into post a comment.