After a long time resuming the Blogging and Why not start with the project which I Love Most. Yes, its Workout Wednesday #WoW. Name itself says WOW challenges and huge thanks to the people to who provide the continuous Challenges daily. Lets decipher this week #WoW challenge.

Requirements:

  • Create a visualization with bar charts that show total sales, total profit, and total quantity by city-state (See below for state abbrevation calculation). Sort by sales.
  • Create a calculation that you can use as a filter that shows cities in groups of 10. Make sure the labels based on the rank i.e. the top ten are 1-10, the 2nd 10 are 11-20, etc.
  • Make sure the bar size remain proportional with the maximum value for each. Do not used a fixed axis.
  • Add a zero line for profit only.
  • Match formatting: Size 11 font, bar color #ED7470.

Step 1: Create a Calculated field State Abbreviation

Credit: Luke Stanke
CASE [State] WHEN “Alabama” THEN “AL”
WHEN “Alaska” THEN “AK”
WHEN “Arkansas” THEN “AR”
WHEN “Arizona” THEN “AZ”
WHEN “California” THEN “CA”
WHEN “Colorado” THEN “CO”
WHEN “Connecticut” THEN “CT”
WHEN “Delaware” THEN “DE”
WHEN “District of Columbia” THEN “DC”
WHEN “Florida” THEN “FL”
WHEN “Georgia” THEN “GA”
WHEN “Idaho” THEN “ID”
WHEN “Illinois” THEN “IL”
WHEN “Indiana” THEN “IN”
WHEN “Iowa” THEN “IA”
WHEN “Kansas” THEN “KS”
WHEN “Kentucky” THEN “KY”
WHEN “Louisiana” THEN “LA”
WHEN “Maine” THEN “ME”
WHEN “Maryland” THEN “MD”
WHEN “Massachusetts” THEN “MA”
WHEN “Michigan” THEN “MI”
WHEN “Minnesota” THEN “MN”
WHEN “Mississippi” THEN “MS”
WHEN “Missouri” THEN “MO”
WHEN “Montana” THEN “MT”
WHEN “Nebraska” THEN “NE”
WHEN “Nevada” THEN “NV”
WHEN “New Hampshire” THEN “NH”
WHEN “New Jersey” THEN “NJ”
WHEN “New Mexico” THEN “NM”
WHEN “New York” THEN “NY”
WHEN “North Carolina” THEN “NC”
WHEN “North Dakota” THEN “ND”
WHEN “Ohio” THEN “OH”
WHEN “Oklahoma” THEN “OK”
WHEN “Oregon” THEN “OR”
WHEN “Pennsylvania” THEN “PA”
WHEN “Rhode Island” THEN “RI”
WHEN “South Carolina” THEN “SC”
WHEN “South Dakota” THEN “SD”
WHEN “Tennessee” THEN “TN”
WHEN “Texas” THEN “TX”
WHEN “Utah” THEN “UT”
WHEN “Vermont” THEN “VT”
WHEN “Virginia” THEN “VA”
WHEN “Washington” THEN “WA”
WHEN “West Virginia” THEN “WV”
WHEN “Wisconsin” THEN “WI”
WHEN “Wyoming” THEN “WY” END

Step 2: Create a Combined Calculated Field

after Creating Calculated field –> Drag the Combined Field to Rows you should get the view like below

if you get the State Abbr, City -> Right Click on combined field –> Edit Combined Field –> Drag the City First and State Abbr after

Step 3: Drag Sales , Profit and Quantity to Columns –> click on Sort

Your view will get sorted on Sales

Step 4: Change the Bar Color to the one Specified by Luke

For Windows
For Mac

Step 5: After changing the color you should get the view like below

Step 6 : Create calculated field to group the City,State Abbr which would be used for pagination

I have written a blog post about Pagination Tableau Check out to understand More.

Step 7: After Creating Pagination Cal, We need to group them. Create below calculation to do the same.

Grouping : STR(([Pagination]10)+1)+”-“+ STR(([Pagination]+1)10)

Step 7 : Drag to Filter — Select Use ALL

Step 8 : Show the Filter –> Select Single Value Slider Option

Step 9: Select Group 1-10, you should get top 1-10 Values like below.

Step 10 : Change the Grouping from 1-10 to 11-20, then your bar length changes and its equal to same as it is in 1-10

as you see above sales in 1-10 , First value is 256,368 in 11-20 is 21,884 but both look like same length as Luke mention maintain the Bar length proportionate along.

Step 11: In order to maintain the Bar length Proportionate create a calculations to capture each measure Max value like below

Sales

Max Sales: MAX({Exclude [State Abbrv & City (Combined)]:mAX(
{Fixed [State Abbrv & City (Combined)]:SUM([Sales])})})

Max Quantity : MAX({Exclude [State Abbrv & City (Combined)]:mAX(
{Fixed [State Abbrv & City (Combined)]:SUM([Quantity])})})

Max Profit : MAX({Exclude [State Abbrv & City (Combined)]:mAX(
{Fixed [State Abbrv & City (Combined)]:SUM([Profit])})})

What exactly above calculations tells about it gives maximum value of measure at the level of City, State Abbr in terms of SQL Statements:
SELECT Sales_Sum FROM (SELECT “City,State”, SUM(Sales) Sales_Sum FROM Orders group by “City,State” )

Step 11: As we created drag the each and every metric to respective marks card

Step 12: After Placing the Fields Create reference line i will showing for Profit since it has another challenge to show zero line you can use the same concept for Other two Metrics

Add the zero line -> Again click on Profit Axis –> Add reference Line select the option shown below

instead of Zero line do the same activity for Sales and Profit

Step 12: After add all the reference lines you should get the view like below

now you can see the difference in the bar length once you change from 1-10 to 11-20

Step 13: We are done with the view lets do some formatting

Formatting 1: Remove Grid Lines and Zero Lines

Formatting 2: Removing the Borders

Formatting 3: Hide the axis and Labels

Formatting 4: Formatting the Numbers

Do the same for Profit , But for Quantity –> Select “Number Custom ” –> Decimal places would be 0

once formatting complete you should get the view like below

Step 13 : Check the below gif to create Dashboard

Final Dashboard looks like below once you follow the steps

Interactive Viz:

Hope this helps. If this blog helps you to Solve the Challenge kindly tag me @NaveenBandla2 along with the contributors
@AnnUJackson@LukeStanke@lorna_eden, and @curtisharris_! along with hashtag #WorkoutWednesday2019

More blog posts coming soon. Happy Weekend.