Let’s take a closer look at this week’s intermediate problem.
Requirements:
- Dashboard size is 850 x 600; tiled; 1 sheet
- Each member of the pictogram is a distinct product name.
- Each row in the pictogram has 10 members.
- Members of each new row are included from left-to-right.
- Sort the product name from highest to lowest frequency.
- Color each group of 100 a differently (I used orange tones).
- Add a small break between each set of 100 members.
- Place the product name and the total sales in the tooltip.
- Add a product label and the total number of distinct product names above the compound pictograms. Make sure the Product names is bold font and the count is regular font.
- Set the minimum and maximum values on the columns axis (x-axis) to -3 and 12, respectively.
- Set the minimum and maximum values on the rows axis (y-axis) to -1 and 32, respectively.
From the requirements here is what we need to focus on:
- We are counting the distinct number of product names.
- For each sub-category there are ten members per “row”.
- After there are 10 members in a row, a new row starts and it goes from left-to-right.
- The members are sorted by quantity.
- Each block of 100 has a different color.
- There are some formatting things to do, too.
Step 1: Build a visualization that kind of solves the problem.
- Add Sub-Category to columns.
- Add product name to detail.
- Right-click on Sub-Category on the columns shelf and select Filter. Use all and then filter Top N. Select By Field and select Top 5 by Product Name, count (distinct).
- Right-click on Sub-Category on the columns shelf and select Sort. Sort descending by field. Select Product Name and count (distinct).
- Right-click on Product Name on the marks card and select Sort. Sort descending by field. Select Quantity and ascending.
- Change the mark type to circle. Adjust the size if need be.
At this point you have the following chart:
Step 2: Build the 2nd-level header
This is relatively straight forward. We want to show the distinct number of products under then Sub-Category. I’m going to write this using an LOD.
## 2nd level header
"(" + STR({FIXED [Sub-Category] : COUNTD([Product Name])}) + " products)"
#^ ^ ^
#| | |
#Create Get product totals for each add label and
#Parenthesis Subcategory parenthesis at end
Lets add this field to the right of [Sub-Category] on columns. Let’s also quickly format both headers. And hide field labels for columns.
So far this is where we are:
Step 3: Use the INDEX()
We will want to sort every single dot from this “unstructured” list. To do so we need to give each point a sense of order. We can do that with the INDEX() function. The INDEX() returns the index of the current row in the partition, without any sorting with regard to value. The first value starts at one and then counts up.
## Index function ## INDEX()
Lets add this to color temporarily. You’ll notice that all the values are one. We need to go baedit the table calculation associated with it. Right-click on the [Index function] on color and select Edit Table Calculation. We’ll want to compute using specific dimensions. Make sure both Sub-Category and Product Name are selected. Also make sure the calculation is running at the level of deepest restarting every Sub-Category.
Here is what you should look like:
Step 4: Create the calculations that will organize the product names into uniform rows and columns.
We need to organize our points in columns and rows. Let’s take a look at how we need to organize it via the following coordinate system:
You’ll see the first value represents the row while the second value represents the columns. You’ll also notice that if we remove the comma then we’re basically counting up by 1 starting at the number 10.
Let’s edit our existing index function by adding 9. This will now make the ones digit the same in columns matrix, and hundreds and tens digits represent the values rows digits.
## Index function ## INDEX() + 9
Columns The first is to create the columns calculation. To do this, We’re going to use the modulo function — which basically provides — the remainder of a division problem, which is also the ones digit.
## Columns ## ([Index function]) % 10 # Remember we updated columns
Rows To create the rows there are a couple of ways to do this. But here’s the easiest way i’ve found to do it:
## Rows ## ([Index function] - [Columns])/10
Adding [Rows] to the rows shelf, and [Columns] to the columns shelf produces the following visualization (you may need to edit your table calculations for both to address all dimensions on the view and restarting every sub-category):
Also: WERE BASICALLY THERE. WE JUST NEED TO ADD SOME SPACE BETWEEN GROUPS OF 100 AND ADD COLOR! (They both involve the same work.
Step 5: Color and Padding
So the last thing we need to do is group the values in “sets” of 100. FYI: I don’t mean a Tableau set, I just mean a collection of values. Using the [Index function] we created our first 100 values run from 10 to 109. There are a couple ways of doing this but the lazy way (which I didn’t do in my actual solution) is just to create an if statement.
## Groups ## IF [Index function] < 110 THEN 1 ELSEIF [Index function] < 210 THEN 2 ELSE 3 END
We're going to drop this [Groups] calculation onto color and turn it into a discrete value. We can then edit the color and make things look pretty.
That gets us this far:
So let's add that space by editing our rows calculation. In the requirements I suggest a space of .5 units. All I need to do is add [Groups]/2 to the existing rows calculation. This will give us spacing of 0.5, 1.0, and 1.5 for each of the respective groups.
## Rows ## (([Index function] - [Columns]) / 10) + ([Groups] / 2)
This edit produces the following chart:
Now all we need to do is finalize the formatting, and edit the axes so that the the range is fixed to the specifications. Then publish.