The last weeks of #WorkoutWednesday have been intense and Lorna’s challenge for week 15 was no exception.

The challenge of week 15 is about creating a chart that shows in a shaded area the range between the lowest and the highest prices reached by the action in each week. Additionally, the weekly closing value of the share must be shown as a circle and a line must link the weekly closing values.

• The exercise is full of multiple challenges; they stand out from them:
• The creation of a chart with three types of “Marks”.
• The use of almighty “SET ACTIONS”, as Lorna qualifies, to achieve the requested functionality.
• The use of LOD calculations.
• The definition of dynamic reference lines according to the preference of the user to use comparison vs. the global average or vs. the value of the stock at a selected date.

Next, I share the steps I followed to build the main chart of the challenge.

STEPS FOR THE DEFINITION OF BASIC VARIABLES

Step 1: Define parameter for the user to select type of comparison.

• Name: Compare Against
• Data type:  Integer
• Allowable values: List
• Value:  1 for Average Close and 2 for Date

Step 2: Create the “Week” variable.

The chart shows the information weekly, so a new variable would be defined that allows grouping the dates belonging to a week, assigning the variable “Week” the value of the date of the first day of the week.

 Week = DATE(DATETRUNC(‘week’, [Date]))

The exercise considers that the week starts on Monday.

To verify that the start of the week is being handled on Monday, it can be checked by selecting the data source within the “Data” menu and then selecting the “Date Properties” option.

Step 3: Create the “Weekly Close” variable.

We use LOD calculations.

 Weekly Close = {FIXED [Week] : MAX(IIF([Date] = {FIXED [Week] : MAX([Date])}, [Close], NULL))}

The formula {FIXED [Week]: MAX ([Date])} allows us to obtain the maximum value of the original date, within each week. That is, the date of the last day of the week according to the records in the source data.

Then another weekly LOD calculation is used to obtain the closing value, selecting only the closing value of the date that coincides with the date of the last day of the week, the other dates will be null.

Step 4: Define the variable “Gray Area”.

As the gray area to show the ranges between the minimum and maximum prices does not start from zero, we have the need to define two areas: one gray and one transparent. For which we can use any of the following techniques: 1) Area technique with “Stack On” and 2) Area technique with “Stack Off”, which we have already seen in previous blogs.

Personally, I like to use the first technique, because although both techniques achieve the same visual result in the chart, I think that when selecting a “mark” of the area chart, the first technique offers a better visual experience, but in the end it is a matter of personal preferences and both techniques have their followers.

To learn more about these techniques you can check the blog “Coloring between lines”.

 Gray Area = MAX([High]) – MIN([Low])

Step 5: Create the variable “Dot”.

This variable is essential for a trick that will be explained later. Any of the following forms can be used for its definition.

 Dot = CHAR(9679) Dot =  “●”

STEPS FOR THE DEFINITION OF REFERENCE LINES

For the definition of the “Reference lines”, the value of the parameter defined in step 1 must be taken into consideration.

If the “Average closing” option is selected, the average closing value ONLY should be used (we will use LOD calculations).

If the “date” option is selected, you must add a reference line for the selected week and have a reference line that shows the closing value for that week (we will use “Set Actions”).

Any point on the chart will change the date reference line to compare up and down.

If multiple points are selected, the maximum week in your selection will be set by default (we will use LOD calculations).

Step 6: Create a Set associated with the variable “Week”

Select the “Week” variable and with the right click select the Create Set option with the name “Set of Weeks”.

Step 7: Create a Set Actions

Let’s remember  that the “Set Actions” take an existing “Set”, in this case “Set of Weeks” and update the values contained in that “Set” based on the action of a user in the visualization.

It’s also possible to define the Set Action later in the Dashboard. In this exercise, I’m using the definition within the “Worksheet” menu.

Step 8: Calculate the maximum week selected by the user.

When the user interacts with the Viz, the Set Actions allow to select multiple “marks”. In this challenge, Lorna tells us that when selecting several points, you must set the maximum week in your selection by default.

 Max Week Selected = {FIXED : MAX(IIF([Set of Weeks], [Week], NULL))}

At this point of the challenge, I would like to have the Tableau 2019.2 version already available, which is in its second beta version, which means that its launch is getting closer and closer. I would love to use the actions of the parameters, a new and powerful functionality that offers us new possibilities of interaction in the Viz. I think that in this challenge, they would come as a glove, since only the last selected brand would be considered.

Step 9: Calculate the values for the reference lines.

 Ref Line for Price = If the “Average closing” option is selected, the average closing value should be used IIF([Compare Against] = 1, { AVG([Weekly Close]) }, If the “date” option is selected, a reference line must be added that shows the closing value for that week. { MAX( IIF([Week] = [Max Week Selected], [Weekly Close], NULL)) })
 Ref Line for Week = If the “Average closing” option is selected, there will be no reference line in the “Week” axis. IIF([Compare Against] = 1, NULL, If the “date” option is selected, you must add a reference line that shows the selected week. [Max Week Selected])

Step 10: Calculate the color variable.

The color of the circles is determined according to whether the weekly closing value is above or below the value of the price reference line.

 Color = [Weekly Close] >= [Ref Line for Price]

STEPS FOR THE DEFINITION OF THE CHART

Step 12: Create chart with synchronized double axis.

Select the option “Stack Marks On” from the “Analysis” menu.

Define the first axis for the area chart:

• Filter ⬅  “Measure Names” and select “Gray Area” & “Low”
• Columns ⬅   “Week” use “Exact Date” and “Continuous”
• Rows ⬅   “Measure Values”
• Color ⬅   “Measure Names”
• Tooltips ⬅   “MIN(Low)”, “MAX(High)”, “MAX(Weekly Close)”, “MAX(Ref Line for Price)” and “Max(Ref Line for Week)”

Adjust the colors to show the “Low” area in white and the “Gray Area” variable in gray.

Define the second axis for the line chart:

For this second axis we will use a trick, where the variable “Dot” created in step 5 will help us to place points in each of the “Marks” of a line chart, giving the effect requested in the challenge.

• Rows ⬅   “MAX(Weekly Close)”
• Color ⬅   “ATTR(Color)”
• Tooltips ⬅   “MIN(Low)”, “MAX(High)”, “MAX(Weekly Close)”, “MAX(Ref Line for Price)” & “Max(Ref Line for Week)”
• Label ⬅   “ATTR(Dot)”

To use the variables “Color” and “Dot” as attributes, select the “Attribute” option with the right click.

Although the original challenge is based on information from May 2013, the following images show only the data for 2018 and 2019, in order to better appreciate the variations between weekly minimum and maximum values.

With this we conclude the main chart of the challenge!

Note: For the construction of the tooltip chart, I use a similar logic, only in the tooltips I use the types of marks of: “Bar” and “Circle”.

Link to Tableau PublicThanks to @lorna_eden for sharing a fun challenge.

If you have any questions about the blog, do not hesitate to contact me on Twitter (@rosariogaunag)

Regards!

Rosario Gauna