% of total, candy dots, circles, small multiples, text, waffle chart, trellis chart, table calc, LOD calc, Workout Wednesday

By: Rosario Gauna @rosariogaunag

In Week 11 of Workout Wednesday, @RodyZakovich prepared us a different and fun challenge, perhaps decided that we deserved to lower the level of stress after the challenge of @LukeStanke of the previous week. Link to the Challenge

In this challenge, Rody asks us to create waffles with circles distributed 20X5 WITHOUT USING BLENDING, WITHOUT SHAPES OF PICTURES, ONLY USING TEXT.

A waffle must be displayed for each of the sub-categories of Office Supplies, with its percentage of sales with respect to the total sales of the category in a blue color and the rest of the points in a gray color.

Next, I detail the steps that I followed:

Step 1: Filter the data by the category of “Office Supplies”

Step 2: Define the coordinates (x, y) for each of subcategories, so that each of the 9 sub-categories will have a unique coordinate within a 3 x 3 matrix.

VARIABLE FORMULA
Calculate the variable “x” or column to be located at each of the subcategories COLUMN =

(INDEX()-1) % INT(SQRT(SIZE())) + 1

In this exercise, we know in advance that 3 columns will be presented, so we can use a simplified formula

(INDEX() – 1) % 3 + 1

Calculate the variable “y” or row to be located at each of the subcategories ROW =

INT((INDEX()-1) / INT(SQRT(SIZE()))) + 1

The simplified formula for three columns, would be

INT( (INDEX()-1) / 3) + 1

Step 3: Calculate the field of “% Sales”. The sales of each sub-category are calculated and divided by the total sales of the category.

          % Sales =

{FIXED [Sub-Category] : SUM([Sales])} / {FIXED [Category] : SUM([Sales])} * 100

Step 4: Calculate the “% Sales Display” field. This formula has the purpose of matching the rounding of “Ceiling” that Rody used or also known as rounding to the nearest larger integer. Initially, I had used symmetric rounding, but I decided that it was better to include a parameter so that the user could decide the type of rounding to apply.

            % Sales Display =

CASE [Choose Round]

    WHEN 1 THEN CEILING([% Sales])

    WHEN 2 THEN FLOOR([% Sales])

    WHEN 3 THEN ROUND([% Sales],0)

END

Step 5: Define the “String Candy Dots” field. Which is composed of a total of 104 characters. 100 characters of circles distributed 20 per line, plus the 4 characters of line break. Here are two of the methods to define the string in Tableau.

DEFINITION MANUAL DEFINITION USING FORMULA
String Candy Dots =

“●●●●●●●●●●●●●●●●●●●●

●●●●●●●●●●●●●●●●●●●●

●●●●●●●●●●●●●●●●●●●●

●●●●●●●●●●●●●●●●●●●●

●●●●●●●●●●●●●●●●●●●●”

String Candy Dots =

REGEXP_REPLACE(SPACE(20),’\s’,’\u25cf’)+CHAR(10)+ REGEXP_REPLACE(SPACE(20),’\s’,’\u25cf’)+CHAR(10)+ REGEXP_REPLACE(SPACE(20),’\s’,’\u25cf’)+CHAR(10)+ REGEXP_REPLACE(SPACE(20),’\s’,’\u25cf’)+CHAR(10)+ REGEXP_REPLACE(SPACE(20),’\s’,’\u25cf’)

Step 6: Subdivide the field of “String Candy Dots” in two, to handle two dot colors.

VARIABLE FORMULA
Calculate the length of the string corresponding to the percentage of sales plus 1 additional character for every 20 points of the percentage of sales. Length 2nd Part =

[% Sales Display]

+ INT([% Sales Display]/20)

Calculate the first part of the points, that is, those of light gray color.

The left characters of the string will be taken, corresponding to the subtraction of 104 (total size of string) minus the length calculated in the first calculation of this step

Candy Dots 1st Part =

LEFT([String Candy Dots],

104 – [Length 2nd Part])

Calculate the second part of the points, that is, the blue ones.

The characters on the right of the string will be taken, corresponding to the length calculated in the first calculation of this step.

Candy Dots 2nd Part =

RIGHT([String Candy Dots],

[Length 2nd Part])

Step 7: Move to the mark type detail (detail) in marks card, the Sub-Category variable ordered descending by the amount of the sales.

 Step 8: Move to label type in marks card, the variables: Sub-Category, % de Sales Display, Candy Dots 1st Part and Candy Dots 2nd Part

Accommodate the four variables within the text shelf, the first two variables in the first row and the last two variables in the second row. And change the texts to the desired colors.

Estante de Texto

Step 9: Move the “Column” variable to the column shelf and the “Row” variable to the row shelf.

Apply to both variables, the option of Calculation of Table using the option of “Compute Using” Sub-Category, Candy Dots 1st Part and Candy Dots 2nd Part

Step 10: Only adjust formats. And ready!

Link Tableau Public 

D_Small Multiple Grids-Final

Thank you very much @RodyZakovich for this fun challenge.

EXTRA EXERCISE

After this Rody exercise, I remembered the @tableauing design of #MakeoverMonday from week 10, where I had small waffles and each point represented a response and the colors assigned indicated the type of response obtained: Correct, Incorrect or Null.

Imagen Rodrigo

He commented that he wished he could order the points by color, but he was still working.

And I just finished Rody’s challenge, it was more than tempting to try the technique in Rodrigo’s design, where basically it was required:

  • The use of three colors, one per response type: 1) Correct, 2) Incorrect, 3) Null
  • Sort colors by type of response
  • Manage a number of variable points or dots per country. (There are countries with 20 respondents, countries with 22 respondents and countries with 26 respondents)
  • It was required to display a maximum of 9 points or dots per line.

For this exercise, you must have previously calculated by country-question, the number of:

  • Correct answers (# correct)
  • Incorrect answers (# incorrect)
  • Total people surveyed (# Total of Respondent)

Step 1: Define “String Candy Dots”. Which is composed of a total of 29 characters. 27 characters of circles distributed 9 by line, plus the 2 characters of line break. Here are two of the methods to define the string in Tableau.

DEFINITION MANUAL DEFINITION USING FORMULA
String Candy Dots =

“●●●●●●●●●

●●●●●●●●●

●●●●●●●●●”

String Candy Dots =

REGEXP_REPLACE(SPACE(9),’\s’,’\u25cf’)+CHAR(10)+ REGEXP_REPLACE(SPACE(9),’\s’,’\u25cf’)+CHAR(10)+ REGEXP_REPLACE(SPACE(9),’\s’,’\u25cf’)

Step 2: Calculate the first sub-string corresponding to the correct answers.

VARIABLE FORMULA
Calculate the number of characters in the string of correct answers.

Its value corresponds to the number of correct answers plus 1 additional character for every 9 correct answers.

Length Correct Answers =

[# Correct]

+ INT([# Correct]/9)

Calculate the first part of the string that corresponds to the correct answers.

The left characters of the string will be taken, corresponding to the length, just calculated.

Candy Dots 1st Part=

LEFT([String Candy Button],

[Length Correct Answers])

Step 3: Calculate the second sub-string corresponding to the incorrect answers.

VARIABLE FORMULA
Calculate the number of characters in the string, the number of answers answered (both correct and incorrect). Length Answers with Response =

[# Correct] + [# Incorrect] +

INT(([# Correct]+[# Incorrect])/9)

Calculate the second part of the string that corresponds to the incorrect answers.

The LEFT () function is used to create a sub-string that corresponds to the total number of answers answered (correct and incorrect).

And to the newly calculated sub-string, the RIGHT () function is applied to create the sub-string of only the incorrect answers

Candy Dots 2nd Part =

RIGHT(

LEFT([String Candy Button], [Length Answers with Response]),

[Length Answers with Response] – [Length Correct Answers])

Step 4: Calculate the last sub-string corresponding to the unanswered or null answers.

VARIABLE FORMULA
Calculate the number of characters in the string of the total number of respondents (with correct, incorrect and unanswered answers) Length Total of Respondent =

[# Total of Respondent]

+ INT([# Total of Respondent]/9)

Calculate the last part of the string that corresponds to the answers without answering or with null value. Candy Dots 3rd Part =

RIGHT(

LEFT([String Candy Button], [Length Total of Respondent]),

[Length Total of Respondent] – [Length Answers with Response])

Step 5: Calculate the number of correct answers per country.

        Label Correct by Country =

{FIXED [Country] : [# Correct]}

Step 6: Move to label type in marks card, the variables: Candy Dots 1st Part, Candy Dots 2nd Part and Candy Dots 3rd Part.

Arrange the three variables within the text shelf in a single line in the required presentation order and change the texts to the desired colors.

Step 7: Move the “Topic” variable to the column shelf and the “Country” and “Label Correct by Country” variables to the row shelf.

Step 8: Only adjust formats. And ready!

Link a Tableau Public

Policymaker Report

Thank you very much @tableauing for sharing your design.

I hope you enjoyed reading this blog.

If you have any questions, do not hesitate to contact me on Twitter (@rosariogaunag)

Regards!

Rosario Gauna

 

Advertisements