Posts in 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 10 Solution

Below is a chart built for #WorkoutWednesday. It seems to have thrown people off a bit.

Just as a reminder here are the requirements:

  • Use sales for 2017.
  • The size of the dashboard 500px x 600px.
  • If you think of the segments as a clock, Week 1 should be at the 12:00 position.
  • The weeks should be evenly spaced based on the week number.
  • You’ll be using geometry. The minimum radius is 1. The maximum radius is 2.3. (Spoilers provide some math help.)
  • The space between the segments is .15.
  • Note the tooltips to get some assistance on your math.

Before we tackle the circle, lets just create a line chart that emulates a bar chart.

Step 1: Provide total control over each line thru a union

It’s important to consider one foundational principle when building any visualization in Tableau: you will only show aggregate information based on the number of dimensions you have on a view – unless you use a level-of-detail calculation. The chart above shows sales by segment and week of the year. Week of year and segment are considered dimensions by Tableau and therefore if we create a view we should have one mark for every combination. And if we create that data we’ll see it’s exactly what we produce. Basically a dot plot.

But we really want a line. and a line is made up of two points. With Tableau its impossible to have two marks per combination on a view. In order to “hack” this line I’m going to use a trick and union the data to itself (Note: there are other ways this can be done but it’s just the way I prefer because I can keep the data balanced as every data point is duplicated twice).

After we union the data to itself. There is a new field in the data called [Table Name] this distinguishes the data sources from each other. My [Table Name] dimension has two members “Order” and “Order$” — yours is likely “sales.csv” and “sales.csv1”.

With this data now duplicated, we can control the start point of a line by using logic statement with [Table Name]. In this case we’re going to build a base calculation that will help us along the way

[Data Level]

[Table Name] = "Order"

Again this is going to basically allow us to control each mark twice — one for each dataset.

Step 2: Build base calculations

When doing analyses like this I like to use level of detail calculations because I don’t have to really think about aggregations based on the marks on the view, they are just aggregated to the level I am working with. Since I know I’m working with week and segment data I’m going to build a few level-of-detail calculations.

[Consumer Sales / Week]

{FIXED DATETRUNC("week", [Order Date]) : 
   SUM(IF [Segment] = "Consumer" THEN [Sales] END)
}

[Corporate Sales / Week]

{FIXED DATETRUNC("week", [Order Date]) : 
   SUM(IF [Segment] = "Corporate" THEN [Sales] END)
}

[Home Office Sales / Week]

{FIXED DATETRUNC("week", [Order Date]) : 
   SUM(IF [Segment] = "Home Office" THEN [Sales] END)
}

[Sales / Week]

{FIXED DATETRUNC("week", [Order Date]) : 
   SUM([Sales])
}

* This actually produces totals twice the value expected because we unioned the data to itself, but will be fine for this analysis since we are doing a percent of total. *

Each of these calculations are going to be very useful as we built out the chart above.

Step 3: Emulate a stacked bar chart

Building this chart is a lot like building a stacked bar chart that shows percent of totals.

Consider how we will build each part of a stacked bar chart for:

Corporate

  • start: 0
  • end: [total % of corporate]

Consumer

  • start: [total % of corporate]
  • end: [total % of corporate] + [total % of consumer]

Home Office

  • start: [total % of corporate] + [total % of consumer]
  • end: 1

We con control the start and end points of each part of these lines using the [Data Level] function we just put together. When [Data Level] is true, let’s say that’s the start of the line and when [Data Level] is false lets say that’s the end of the line. The function would then look like this

Line Location

IF [Data Level]           // Returns TF
THEN CASE [Segment]       // For true create start of each line
     WHEN "Corporate" THEN 0
     WHEN "Consumer" THEN [Corporate Sales / Week]/[Sales / Week]
     WHEN "Home Office" THEN ([Corporate Sales / Week] + [Consumer Sales / Week])/[Sales / Week]
     END
ELSE CASE [Segment]       // For false create end of each line
     WHEN "Corporate" THEN [Corporate Sales / Week]/[Sales / Week]
     WHEN "Consumer" THEN ([Corporate Sales / Week] + [Consumer Sales / Week])/[Sales / Week]
     THEN "Home Office" THEN 1
     END
END

Let’s create a line chart to see what this produces.

Add week of [Order Date] to Columns. Add [Line Location] to rows and make it a continuous dimension. Add [Segment] to color, change the mark type to line, add [Table Name] to path, and also add week of [Order Date] to detail. Voila! A line chart that looks like a bar chart!

Let’s get the spacing to work out. Edit your [Line Location] calculation. We’re going to make it easy and hard code the spacing in.

Line Location

IF [Data Level]           // Returns TF
THEN CASE [Segment]       // For true create start of each line
     WHEN "Corporate" THEN 0
     WHEN "Consumer" THEN [Corporate Sales / Week]/[Sales / Week]+ .15
     WHEN "Home Office" THEN ([Corporate Sales / Week] + [Consumer Sales / Week])/[Sales / Week]+ .30
     END
ELSE CASE [Segment]       // For false create end of each line
     WHEN "Corporate" THEN [Corporate Sales / Week]/[Sales / Week]
     WHEN "Consumer" THEN ([Corporate Sales / Week] + [Consumer Sales / Week])/[Sales / Week]+ .15
     THEN "Home Office" THEN 1+ .30
     END
END

This hard-coding adds a nice space to our chart.

Believe it or not you are almost there for the whole thing!

Step 4: Unit Circle time!

They key to all of this is understanding geometry from your youth, particularly the formula for a circle:

[radius] * sin(2π * [% of circle]) and/or [radius] * sin(2π * [% of circle])

I’ll give you a hint, we just built our radius sans one tiny thing, so all we need is to figure out the % of the circle. And the percent of the circle is just the percent of year completed. Remember that we want basically Week 1 to be equal to zero based on the requirements. so:

Percent of Year

// This returns the percent of the year completed for each week
( DATEPART("week", [Order Date]) - 1 )
/
{FIXED YEAR([Order Date]) : MAX(DATEPART("week", [Order Date]))}

Now we can quickly build our [x] and [y] calculations:

x

[Line Location] * SIN(2*PI() * [Percent of Year])

y

[Line Location] * COS(2*PI() * [Percent of Year])

Now let’s build a view. Add [x] to columns and [y] rows. Make sure they are both continuous dimensions. Change the mark type to line. Add [Segment] to color, [Table Name] to Path, and week of [Order Date] to Detail. Add [Order Date] to filter and select just the year of 2017.

That produces this view:

You are actually so very close. The problem is the start point or our radius — [Line Location] — we want the minimum value to be 1 and the maximum value to be 2.3. Our current minimum and maximum values are 0 and 1.3. You can tell this by going back and looking at the last line chart we created where [Order Date] was on the columns shelf.

All we need to do is edit our existing [Data Level] calculation and add +1 right at the beginning.

Line Location

1 + 
IF [Data Level]           // Returns TF
THEN CASE [Segment]       // For true create start of each line
     WHEN "Corporate" THEN 0
     WHEN "Consumer" THEN [Corporate Sales / Week]/[Sales / Week] + .15
     WHEN "Home Office" THEN ([Corporate Sales / Week] + [Consumer Sales / Week])/[Sales / Week] + .30
     END
ELSE CASE [Segment]       // For false create end of each line
     WHEN "Corporate" THEN [Corporate Sales / Week]/[Sales / Week]
     WHEN "Consumer" THEN ([Corporate Sales / Week] + [Consumer Sales / Week])/[Sales / Week] + .15
     THEN "Home Office" THEN 1 + .30 
     END
END

This changes our chart to:

Now with a little formatting we’ll have the chart we’re looking for: