In this first week of August we have a challenge from a special guest: Klaus Schulte, former Iron Viz champion.

Klaus tells us that during his talk at TCE in Berlin, he showed how to create Step Area charts using a copy of additional data. After his presentation, Ann asked him (he assumes he already knew) if there was a way to create such a graph without duplicating the data using Table Calculations. Well, Klaus could already see if it exists.

Therefore, the challenge is to create a Step Area chart without duplicating data.

Remember a Step Line chart is similar to a traditional line chart, but instead of joining points, lines that represent each period are joined, using vertical lines at 90-degree angles to connect each horizontal line. In a Step Area chart additionally the area between the line segments and the X axis is filled with color or a pattern.

This type of graph is used to highlight the irregularity of the changes, that is, it allows to draw attention to the tendency to show periods without changes and emphasizes the exact time of each change (as well as its magnitude).

In this blog I share the procedure that I use for the creation of this type of graphics and which we had previously talked about in the “Dynamic Chart of Step Line” blog.

However, the requirements of this new Klaus challenge, of identifying the greatest positive and negative variations in the year within the Step Area graph, required very interesting new formula definitions.

The technique we will use considers that it offers the following benefits:

  • Accuracy in the location of the points when presenting horizontal lines in each of the periods.
    • Including the horizontal lines in the initial period and the final period.
    • Very useful option when the periods evaluated are few, for example, when working with the twelve months of the year.
  • Allow to consult the tooltips both at the beginning and at the end of each horizontal line.

It’s important to indicate that to create each horizontal line of each period, two points or “marks” are required. Why? Because the first “mark” is the starting point of the line and the second “mark” is the end of the metric line of the analyzed period.

In this exercise we use a technique of creating the two marks based on an existing dimension, with the benefit that it isn’t necessary to duplicate the records of the table, as in the technique of Table Calculations with Measure Names and Measure Values

Next I share the steps of my final solution:

Step 1: Create a dimension with two values to identify the start and end point of each horizontal line of each period.

The trick is to generate the new variable with only two possible values based on another existing dimension, in this case the new variable is derived from the “Order Date” day.

  • For the even days the new variable is given the value of Zero
  • For odd days the new variable is given the value of One
Dot = DATEPART(‘day’, [Order Date]) % 2

This calculation assumes that you always have at least one odd day and one odd day in each period to have the two marks required for each horizontal line. If this condition is not met, another dimension can be used to define the two marks.

Convert to dimension the new variable.

Step 2: Calculate the start date of each period.

Month = DATE(DATETRUNC(“month”, [Order Date]))

Step 3: Calculate the date value of each of the two points of each period.

This calculation allows to determine the values of the start and end date of each horizontal line corresponding to each period on the x-axis.

Condition Date Axis =
First dot

The value of the date of the first day of the analyzed period is assigned.

IIF([Dot] = 0, [Month],
Second dot

The value of the last day of the analyzed period is assigned.

(First day of the next period – 1 day)

DATEADD(“month”, 1, [Month]) – 1 ))

Step 4: Calculate the value of sales by Category-Month.

Sales by Category = { FIXED [Category], [Month]: SUM([Sales]) }

It’s important to note that the result of the LOD calculation gives us the value of total monthly sales by Category. It doesn’t matter if sales are made on an even day or on an odd day.

Step 5: Calculate the value of sales variations by Category-Month vs. the previous point, the previous month and the following month.

Var Previous Dot =

ZN(SUM([Sales by Category])) –

LOOKUP(ZN(SUM([Sales by Category])), -1)

Var Previous Month =

IIF(ATTR([Dot]) = 1,

ZN(SUM([Sales by Category])) – LOOKUP(ZN(SUM([Sales by Category])), -2),

ZN(SUM([Sales by Category])) – LOOKUP(ZN(SUM([Sales by Category])), -1))

Var Next Month =

IIF(ATTR([Dot]) = 0,

ZN(SUM([Sales by Category])) – LOOKUP(ZN(SUM([Sales by Category])), 2),

ZN(SUM([Sales by Category])) – LOOKUP(ZN(SUM([Sales by Category])), 1))

Step 6: Identify the points with the greatest positive and negative variation with respect to the previous point.

Color  =

IIF([Var Previous Dot] = WINDOW_MAX([Var Previous Dot]), “Rise”,

IIF([Var Previous Dot] = WINDOW_MIN([Var Previous Dot]), “Drop”, “None”))

Step 7: Identify the previous point of the points identified in the previous step and assign the same value.

Color Adjust =

IIF([Color] = “Drop” OR LOOKUP([Color], 1) = “Drop”, “Drop”,

IIF([Color] = “Rise” OR LOOKUP([Color], 1) = “Rise”, “Rise”, “None”))

 Step 8: Create a new variable to define the lines with the greatest positive and negative variations with respect to the previous month.

For which, for the four points identified in the previous step, the value of the monthly sales of the category will be assigned. For the rest of the points the value of null will be assigned.

Line =

IIF([Color Adjust] = “Drop” OR [Color Adjust] = “Rise”,

SUM([Sales by Category]), NULL)

Step 9: Create the variables to be used on the shelves of “Label” and “Tooltips”

Label shelf of the first axis:

Initial and final sales by Category

Label First or Last =

IIF(FIRST() = 0 OR LAST() = 0,  SUM([Sales by Category]), NULL)

Label shelf the second axis:

Value with the greatest positive and negative variations.

Label2 Blue =

IIF([Color Adjust] = “Rise” AND ATTR([Dot]) = 0, ABS([Var Previous Month]), NULL)

Label2 Red =

IIF([Color Adjust] = “Drop” AND ATTR([Dot]) = 0,  -ABS([Var Previous Month]), NULL)

Tooltips shelf of the second axis:

To create custom Tooltips signs for the points that form the lines with the greatest variations.

Label3 =

IIF([Color Adjust] = “Drop” OR [Color Adjust] = “Rise”,

IIF(ATTR([Dot]) = 0,  “Change vs. Previous Month:”, “Change vs. Next Month:”), NULL)

Label3 Blue =

IIF([Color Adjust] = “Rise”, 1, NULL) *

IIF(ATTR([Dot]) = 0, ABS([Var Previous Month]), ABS([Var Next Month]), NULL)

Label3 Red =

IIF([Color Adjust] = “Drop”, -1, NULL) *

IIF(ATTR([Dot]) = 0, ABS([Var Previous Month]), ABS([Var Next Month]),  NULL)

Step 10: Create a graph with synchronized double axis:

All table calculation fields must be defined computed using “Pane Across” including “Nested Calculations”

For the area chart

  • Filter ⬅   Filter data for the year 2018.
  • Columns ⬅   UPPER([Category]) and “Date Axis”
  • Rows ⬅   “Sales by Category”
  • Color ⬅   “Category”
  • Label ⬅   “Label First or Last” and “Var Previous Month”

For the line chart

  • Rows ⬅   “Lines”
  • Color ⬅   “Color Adjusted”
  • Label ⬅   “Label2 Blue” and “Label2 Red”
  • Tooltips ⬅   “Label3”, “Label3 Blue” and “Label3 Red”

Verify that all table calculation fields must be defined computed using “Pane Across”.

Step 11: Adjust colors and formats. Ready!

My solution is available here.

Picture1

In summary:

A Step Line chart is formed by joining lines that represent each period, using vertical lines at 90-degree angles to connect each horizontal line.

Taking this definition, to create the horizontal lines of a Step Line graph, two points are required for each period: one for the beginning of the period and one for the end of the period.

There are several techniques to have two points in Tableau, the technique that I use to create the two points per period without duplicating the data, requires the simulation of working with two copies of the data, but in reality, what we do is separate the data in two groups (even days to create the first point and odd days for the second point), therefore, the use of  LOD calculations is required to present the total amounts, regardless of the group of data being worked on.

This technique is ideal for when we are working with a reduced number of periods, such as when working with the months of one year, since each horizontal line represents each month and also provides flexibility in defining the tooltips in each of the marks.

Thank you very much and if you have any questions about the post, do not hesitate to contact me on Twitter (@rosariogaunag)

Regards!