Posts tagged Solution

Solution: Paying The President

We’re working on providing different solutions for the rest of the year. While it’s not a guarantee that we’ll have one every week, we want to dispel any myths around the difficulty of the problems.

Last week Curtis made a video on how he created the solution. He’s going to do this every week. Curtis didn’t get it perfect but that’s okay it’s really close!

I’m not going to show you how to do every part, but I will show you a few tips that will get you going!

First let’s review the modified brief that I’ll use for this demo:

  • Dashboard size: 900px x 620px.
  • Background color: #F9F8F7
  • The data has unknown dates. Make all unknown values show up 3 months past the maximum date. If you need help see SPOILERS #1 below for the formula.
  • Create the stacked rectangle plot at the Source level. Use Size to size the boxes. Place a space of 0.01 between each of the squares. Make sure the order of the boxes matches, too. *This is the key challenge this week*
  • Create an “axis” that labels the start of each year as black and abbreviated month labels of April, July, and September, respectively. Also label the unknown values with “Unknown”. See SPOILERS #2: for help with the “axis”. And see SPOILERS #3 for the month label.
  • Color by source_group: Donald J Trump For President: #F7C480, Other: #76B7B2, Taxpayers: #555555
  • Add annotations for when Trump announced candidacy, gets nomination, the election, and inauguration. You’ll want this: ▼
  • Add the annotation around incomplete/missing data.
  • Add the divider and bracket on the dashboard.
  • Match tooltips.
  • Credit the original authors/designers and link to the original:
    Originally by Derek Kravitz and Derek Willis, ProPublica, and Paul Cronan, Mark Schifferli and Charlie Smart, Fathom Information Designhttps://projects.propublica.org/paying-the-president/.

So were talking about just 3 points. And I give you help with two of them!

Step 1: Unknown Dates

First you need to figure out the unknown dates.

IFNULL([Date], DATEADD("month", 3, {MAX([Date])}))

Put this on Columns and make it date value at the month level.

You’ll end up with this:

Step 2: Create mark logic + add level of detail.

Next were going to figure out the spacing for the marks. The formula utilizes the height of each square and then adds .01. After that we subtract half the hight of the square to center the mark where it should be. The final formula looks like this.

RUNNING_SUM(SQRT([Size]) + .01) - SQRT([Size])

Set your mark type to square and it produces this visual:

The sizing calculation before the appropriate marks are ordered on the marks card.

Next up: Let’s add the correct level of detail to the marks card. Add exact date, source to detail. Source (group) to color, and Size to size. That will give you an imperfect visual where you need to edit the table calculation (be sure to re-order the values).


Once you have edited this you end up with this visual:

It’s almost there!

It’s so close!

Step 3: Dual Axis of MIN(-3) and format.

So were basically there already. Now come’s the trick with the axis. I created a dual axis chart with the second value just an in-line calculation of MIN(-3). Synchronize this and remove all the values from this marks card.

After that you just need to create two calculations. One for the bold font and one for the gray font. Then place these on text on the marks card and format.

Years calculation (also in spoilers):

IF MONTH([Date]) = 1
OR DATETRUNC("month", [Date]) = {MIN(DATETRUNC("month", [Date]))}
THEN STR(YEAR([Date]))
ELSEIF DATETRUNC("month", [Date]) = {MIN(DATETRUNC("month", [Date]))}
THEN "Unknown"
ELSE "" END

Month Calculation: 

IF DATETRUNC("month", [Date]) != {MIN(DATETRUNC("month", [Date]))}
THEN IF MONTH([Date]) = 4
    THEN "APR"
    ELSEIF MONTH([Date]) = 7
    THEN "JUL"
    ELSEIF MONTH([Date]) = 10
    THEN "OCT"
    ELSE ""
    END
ELSE ""
END

Now we just add these to text on marks and format like this:

Year and Month labels.

Then we have this:

Note the faux axis labels.

Step 4: Format

Finally we need to revers our axis, hide our axes, and format. Then we end up with the final product looking like this:

Not bad.

Not bad for 4 steps!

Week 15 Solution

Let’s take a closer look at this week’s intermediate problem.

click to view on Tableau Public

 

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:

  1. We are counting the distinct number of product names.
  2. For each sub-category there are ten members per “row”.
  3. After there are 10 members in a row, a new row starts and it goes from left-to-right.
  4. The members are sorted by quantity.
  5. Each block of 100 has a different color.
  6. There are some formatting things to do, too.

 

Step 1: Build a visualization that kind of solves the problem.
  1. Add Sub-Category to columns.
  2. Add product name to detail.
  3. 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).
  4. Right-click on Sub-Category on the columns shelf and select Sort. Sort descending by field. Select Product Name and count (distinct).
  5. Right-click on Product Name on the marks card and select Sort. Sort descending by field. Select Quantity and ascending.
  6. 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.

click to view on Tableau Public