First off, I wanted to thank everyone who viewed or gave feedback on the Presidential Stocks viz that Luke Stanke and I published on Tableau Public that won Viz of the Day on October 15th, 2019. Secondly, I wanted to give a huge thank you to Luke Stanke for putting up with me during the creation of this viz. I’m not the easiest person to work with, so thanks for giving me just the right amount of push back and insightful feedback to fuel my creativity.
This blog post is going to cover one aspect of our viz, and that is the Parameter Action we used to select the Presidential comparison time frames at the bottom of the viz. By clicking on the year values above the running line chart, the lines are highlighted up until that year point. The cumulative change in the S&P values under each President’s logo also change reflecting the percent change in the President’s tenure up until that point (ex. 1-yr in).
Setting Up the Parameter
The main objective of this chart is to be able to compare Presidents based upon their time in office and the cumulative change of the S&P 500 market index during that time period. Essentially the logic we wanted to use is, “at Day X in Office for President Y, the S&P 500 Index had gained/lost Z%.” and then be able to compare those values. That formula will look something like this:
IF [Days in Office]=[Day in Office Parameter] THEN [Cumulative Change] END
This standard parameter calculation will return the cumulative change of the stock market if the days in office exactly matches the dates of the S&P we had for each President. There are a couple of issue with this. First off, the stock market is generally open M-F, and the weekdays assigned to each day change every year, so day 200 in office for President A might be a Monday but for President B is might be a Saturday and hence will return no value.
The formula that we used for finding the maximum point for each presidential cumulative change line is below. What this is saying in plain English is that for each President, if the days in office field is less than or equal to the field [head in Line Selector] (which is controlled by our Parameter), then return the Cumulative Change field. This makes sure to account for the different days of week or holidays when the market would not be open.
IF [Days in Office] = {FIXED [President] : MAX(
IF [Days in Office] <= [head in Line Selector]
THEN [Days in Office]
END
)}
THEN [Cumulative Change]
END
The [head in Line Selector] field calculation (mentioned in calc above) is show below. The last day we pulled data was on day 992 of his Presidency so that is why that number represents ‘Trump Today.’ Another note, the +1 and +2 days added to the later years is because of leap years.
CASE [Time Frame Compare]
WHEN 'Trump Today' THEN 992
WHEN '1yr' THEN 365
WHEN '2yr' THEN (365*2)
WHEN '3yr' THEN (365*3)
WHEN '4yr' THEN (365*4)+1
WHEN '5yr' THEN (365*5)+1
WHEN '6yr' THEN (365*6)+1
WHEN '7yr' THEN (365*7)+1
WHEN '8yr' THEN (365*8)+2
END
The [Time Frame Compare] field is our Parameter field that is able to change. The logic here is that when you select (or click on in the Parameter Action) this option, like 2yr, that the logic we detailed in the first formula will not show the value (365*2). In this example our new logic will look like below:
IF [Days in Office] = {FIXED [President] : MAX(
IF [Days in Office] <= (365*2)
THEN [Days in Office]
END
)}
THEN [Cumulative Change]
END
This will now display the stock market cumulative change for the first 730 days of each Presidency. In order to make sure that Presidents lines are colored by the appropriate party we used the formula below. The parties were then colored by the appropriate colors (Red=Republican, Blue=Democrat), then the NULLS, or values that did not fall within the days parameter, were colored gray.
IF [Days in Office] <= [head in Line Selector]
THEN [Party]
END
Here is a look into how we structured this view in Tableau Desktop. The [Head in Line] dual axis (calc detailed above) is then used to create the labels as shown in the chart below.
Now for the Parameter Action
At the top of this section we used a selector that looks like a gray bar with the different time selectors present on the bar. This chart element is complicated so I will show how we constructed this first.
Here is the set up we used for the bar in Tableau Desktop.
The [Day Selector Text (padding)] field is below:
IF [Day Selector Text Parameter] = [Day Selector Text]
THEN
IF [Day In Office]=922 THEN 1
ELSEIF [Day In Office] <= 365 then 3
ELSEIF [Day In Office] <= (365*2) then 4
ELSEIF [Day In Office] <= (365*3) then 5
ELSEIF [Day In Office] <= (365*4)+1 then 6
ELSEIF [Day In Office] <= (365*5)+1 then 7
ELSEIF [Day In Office] <= (365*6)+1 then 8
ELSEIF [Day In Office] <= (365*7)+1 then 9
ELSEIF [Day In Office] <= (365*8)+2 then 10
ELSE NULL END - .5 +
IF [Table Name] = "Daily Quotes"
THEN .5 * 2
ELSE 0
END
ELSEIF [Day In Office] = 1 THEN .45
ELSEIF [Day In Office] = 2 THEN 10.55
END +
IF [Day Selector Text Parameter] != "Trump Today"
AND [Table Name] = "Daily Quotes"
THEN -.2
ELSEIF [Day Selector Text Parameter] != "Trump Today"
AND [Table Name] != "Daily Quotes"
THEN .2
ELSE 0
END
The [Day Selector Text (float)] field is below:
IF [Day In Office] <= 2
THEN NULL
ELSEIF [Day In Office]=922 THEN 1
ELSEIF [Day In Office] <= 365 then 3
ELSEIF [Day In Office] <= (365*2) then 4
ELSEIF [Day In Office] <= (365*3) then 5
ELSEIF [Day In Office] <= (365*4)+1 then 6
ELSEIF [Day In Office] <= (365*5)+1 then 7
ELSEIF [Day In Office] <= (365*6)+1 then 8
ELSEIF [Day In Office] <= (365*7)+1 then 9
ELSEIF [Day In Office] <= (365*8)+2 then 10
ELSE NULL END
Essentially what these two fields are doing by using the dual axis is plotting out the appropriate spacing for the text to be displayed. Both the light gray and dark gray areas of the bar are lines charts that we color differently based upon the selection. You just have to play with the sizing of the line to make sure they are bigger than the text. The field used to color the line segments is [Day Selector Text (COLOR)]:
IF [Day Selector Text Parameter] = [Day Selector Text]
THEN "BLUE"
ELSEIF [Day In Office] <= 2 THEN "BKG"
END
Now that we created a line with the options for our parameter we set up the Parameter Action in the dashboard. We created the sheet with the selector ribbon that we created as the source of the action, then the field that determines the color of the line is the field that gets changed based upon the input from the parameter.
Setting Up the Parameter Action
Essentially, the [Time Frame Compare] Parameter field is the field that says whether we want to see Now, 1yr, 2yr etc. This field changes the [Head in Line Selector] field which is part of the formula for how the line chart is colored in the [Day in Office Parameter Color] field. This attribute is then dragged to the color marks card to determine if that day should be colored by the President’s party or not.
The result of this function is to be able to click on the years identified in our selector ribbon and being able to compare the S&P 500 by those time periods as defined in our calculations.
If you want to further investigate how this dashboard was made, click this link to my Tableau Public profile and download the workbook to check it out for yourself.
Thanks so much for reading! Hopefully, this blog was helpful in understanding how we used Parameter Actions to complete our latest Presidential Stocks Viz.
Do you have more questions about Tableau? Talk to our expert consultants today and have all your questions answered!
