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:

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 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:

Then we have this:

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 for 4 steps!