Unicode, ASCII, Shapes, Bins, LOD, Table Calc, Makeover Monday

By: Rosario Gauna @rosariogaunag

For week 24 of the Makeover Monday challenge, Eva presented us with a “Tourism Density Index” graph.  Link to the challenge

The index represents the number of tourists in a country compared to the total population of the country. Here is the original graphic of the challenge. Picture01

For my redesign, I decided to use two graphics, one for each country category. And each graph with its own indicator:

  • “Overtourism” ➡ number of tourists for each local person.
  • “Under tourism” ➡ number of local people for each tourist received.

As both indicators are about number of people, I decided to use human images or silhouettes to represent their value.

In this blog, two techniques to represent a value in multiple images will be detailed:

  • Technique Only Text? with the use of ASCII characters

Character ASCII 50883  웃o 웃U+C6C3

Picture02

  • Technique of bins with the use of shapes

Picture03

The first technique gives us the advantage of being very simple and also allows to combine images and words.

The second technique offers us the advantage of expanding the options of possible images from which to choose, however, I consider that it requires a little more work.

 TECHNIQUE 1: ONLY TEXT?

In this exercise, we work with a simpler version of the one developed in my blog  Small Multiple Candy Points Only with Text?

Step 1: Define two text fields with the ASCII character selected as the image.

The length of the first of the text fields must be equal to or greater than the maximum value to be plotted. The maximum value according to the data of this exercise is 43, therefore, any value equal to or greater than 43 can be used. In the formulas I will use a definition of a field of 50 characters.

The second of the fields will be defined as a single character, which will be used to graph the element of the opposite color against which the comparison is made.

Here are two of the possible ways to define fields with the same ASCII character, but different length of text field, the first of 50 characters and the second of only one character:

String = REPLACE(SPACE(50), ” “, CHAR(50883))
String of One = “웃”

The following steps focus on the definition of the first of the graphs: The “Overtourism” graph.

Step 2: Calculate the text field “String Overtourism”. The “String” field was defined as 50 characters and a new text field will be created, limiting its length to the indicator of the proportion of number of tourists per local person.

String Overtourism =

Left([String], ROUND([Locals Outnumbered By Tourists By], 0))

Step 3: Filter using the “Category” field and select the elements with the value of “Overtourism”.

Step 4: Select the “Text” graphic and move to the shelves of:

  • Rows ⬅  Country” and sort by “Rank”
  • Text ⬅  “String Overtourism” and “String of One”
  • Tooltips ⬅  “Locals Outnumbered By Tourists By”, “Number Of Locals”, and “Number Of Tourist”

Step 5: Select the “Text” shelf and adjust colors and wording according to your preferences.

Picture04

Step 6: Select the “Tooltips” shelf and adjust colors and wording according to your preferences.

Picture05

With step 6 the definition of the first of the graphs is concluded.

Then proceed with the procedure of the second of the graphs: “Undertourism.

Step 7: Calculate the indicator of the proportion of local people for each tourist received.

Locals By Tourist =

[Number Of Locals] / [Number Of Tourists]

Step 8: Calculate the text field “String Undertourism”. If you remember the “String” field, 50 characters were defined and a new field must be created, limiting its length to the indicator of the proportion of the number of local people for each tourist received.

String Undertourism =     

     Left([String], ROUND([Locals By Tourist], 0))

Step 9: Repeat the procedure from steps 3 to 6, replacing the “Overtourism” data with the “Undertourism” data to create the second graph.

Ready! Simple, True?

Link to Tableau Public   Link to Tableau Public

Picture06

 

TECHNIQUE 2: USE OF BINS

Using this technique offers us the advantage of expanding the options of possible images from which to choose.

Step 1: Define the “People” field.In this technique I considered it convenient that the indicators of “Overtourism” and “Undertourism” will be managed in a single variable called “People”, instead of two indicators.

That is, the calculation of the variable “People” will depend on the category of the country. In the case that the category is “Overtourism” it will indicate the number of tourists for each local person and if not, it will indicate the number of places for each tourist received.

People = IIF([Category]=”Overtourism”,

ROUND([Number Of Tourists] / [Number Of Locals], 0),

ROUND([Number Of Locals] / [Number Of Tourists], 0))

Step 2: Define the “People Adjusted” field.This field will be used to create the bins, for which I need to ensure, that at least one record has the value of one and at least one record has the maximum value plus one.

People Adjust =

IIF([People] =

{FIXED : MIN([People])}, 1,

{FIXED : MAX([People])} + 1)

Two LOD calculations are used within the formula. One to obtain the minimum value and another to obtain the maximum value of the variable “People”.

The country that coincides with the minimum value will be assigned the value of one.

Why the value of one? To ensure that the bins always start in one.

The rest of the countries will be assigned the maximum value + 1.

Why is one added to the maximum value? To include an additional image by country, with the opposite color to represent the comparison of the indicator.

Step 3: Create the Bins field.

Select the “People Adjusted” field and with the right button choose the option to create “Bins”. The Bins field created will have a minimum value of 1 and a maximum value of 44.

Proceed to adjust the size of the bins to 1.

Picture07

Step 4: Define the “People By Country” field.This calculation allows us to have in each of the bins of the country the value of the number of “People”.

If the People field will be used directly in the tooltips or in any other calculation, only one of the bins will have information (Only the bins that corresponds to the value of the country) and in the rest of the bins the value will be null.

For this reason, the “People by Country” field is created to count the “People” information for each bin, taking the maximum value of the variable considering a “Table (across)” calculation.

People by Country = WINDOW_MAX(SUM([People]))

Step 5: Define the “People Filter” field.Given that in all the bins they would display an image, this calculation allows us to filter to show only the images that correspond to the value of the country indicator.

People Filter = RUNNING_SUM(1) <= [People by Country] + 1

After the field is created, it is applied as a filter, selecting the “True” values.

Step 6: Define the variable “Is Last?” The field will help us identify the last element deployed for each country.

Is Last? = RUNNING_SUM(1) = [People by Country] + 1

Step 7: Select the type of “Shape” graphic and choose the image to use.For this exercise I chose one of the images in the “Gender” category of the Shapes catalog by Tableau.

Move to the shelves of:

  • Rows ⬅  “Category” and with the right click deselect the option “Show Header”
  • Rows ⬅  “Country” sort by “Rank”
  • Tooltips ⬅  the dimensions you want to include
  • Tooltips  ⬅  the measures you want to include (remember to calculate them with WINDOWS_MAX to ensure that each value is shown in each bins)

Step 8: Define the color according to the combined values of the “Category” and “Is Last” dimensions:

  • Color ⬅ “Category”
  • Tooltips ⬅ “Is Last?”
  • Select the icon to the left of the “Is Last?” Variable and change the “Tooltips” option to the “Color” option. Proceed to adjust colors.

Picture08

Step 9: Duplicate the sheet.

Step 10. Hide or exclude in each of the graphs the countries that you do not wish to list.

It must be taken into consideration that in the case of the “Overtourism” graph the maximum bins to be displayed is 15, whereas in “Undertourism” the maximum value is 44.

In the case of handling independent graphics, the size of the images is adjusted to the maximum value of bins used, therefore, in the “Overtourism” graphic, the images will be larger than the “Undertourism” images, unless let’s apply some small trick to maintain synchronicity between both graphs.

This with the aim of fulfilling one of the lessons with which Eva and Andy reinforce each week “The lesson of equal axes”. Although in this example properly speaking they are not axes, I consider that it also applies and must maintain the synchrony in distances and sizes of the images.

If you want to delve into the meaning of the theme of “equal axes” see lesson 1 of Eva’s blog of  Week 24: Tourism Density Index

To keep the size synchronized between both graphs, one of the following options can be used:

Option A – Do not exclude the country with the highest value of bins and cover it with another element of the dashboard.

That is, do not hide or filter the country with the highest value and order the graph to appear at the bottom of the graph.

Later on, the dashboard uses at least one floating element to overlap and cover only the last line that corresponds to the country of greatest value.

In my exercise, I superimposed two elements:

  • First: A “Blank” element.
  • Second: The top part of the “Undertourism” graph.

Option B –Another way to adjust, is to reduce the width of the graph with fewer bins displayed, until the effect of distances and sizes of the images is synchronized with the graph with the largest number of bins.

Step 11: Select the “Tooltips” shelf in each graph. Adjust wording and colors to your preference.

Step 12: We finish! Link to Tableau Public

Picture10

Once you have reviewed the two techniques, which technique do you prefer? and / or Which technique best suits your needs?

I take this opportunity to thank Eva and Andy for offering us every week, interesting challenges in Makeover Monday.

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

Regards!

Rosario Gauna

Advertisements