Step line, LOD calc, Running_sum

By: Rosario Gauna @rosariogaunag

One of the new features that Tableau 2018.1 offers us, is the ability to visualize non-continuous data to a single click, by allowing any linear chart to be converted into a Step Line chart.

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.

If you still do not have the new version of Tableau 2018.1 and you need to have a Step Line chart, there are several techniques to achieve this type of chart.

The technique that I present below 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.
  • It allows you to consult the tooltips both at the beginning and at the end of each horizontal line.
  • Flexibility to exchange the query of amounts for the period to the accumulated amounts (RUNNING SUM).

Picture1

In this example, to consult the data of Superstore the user has the following options to define the chart:

  • Select the period for the Step Line: Annual, quarterly or monthly.
  • Select monthly sales or accumulated sales.
  • Select total sales or by category or region or segment.

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

One of the ways to achieve the two necessary points to build each line, is with the “Union” technique that we already used in a previous exercise seen in the blog of  My First Radial Chart , where we join the table of Superstore with itself, to be able to count on the two necessary marks.

In this exercise we use a technique of creating the two “marks” based on an existing dimension, with the benefit that it does not require duplicating the records of the table, achieving the same effect as the “Union” technique.

Step 1: Define the parameters for the user to select among the allowed options. The parameters are three: “Accumulated Sales?”, “Detail?” And “Type Period?”

Picture2

Step 2: Create a dimension with two values to identify the start and end points of each horizontal line.

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 day of the “Order Date”.

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

This calculation is based on the assumption that you always have at least one even day and one odd day in each period to have the two marks required for each horizontal line. If this condition is not met, the “Union” technique can be used to define the two marks.

Convert the new “Points” variable to dimension.

Step 3: Calculate the start date of each period.It is calculated according to the option selected by the user in the period type parameter: Annual, Quarterly or Monthly.

Initial Date of the Period = DATETRUNC([Type Period?],[Order Date])

 Step 4: 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 on the x axis.

Condition Date Axis =
First point or Initial Point

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

IIF([Points] = 0, [Initial Date of the Period],
Second Point or Final Point

The value of the date of the first day of the nextperiod is assigned.

DATEADD([Type Period?],1,[Initial Date of the Period]))

Step 5: Calculate the level of detail to determine the color of the lines.It is calculated according to the option selected by the user.

Detail  =

CASE [Detail?]

WHEN 1 THEN “Total”

WHEN 2 THEN [Category]

WHEN 3 THEN [Region]

WHEN 4 THEN [Segment]

END

 Step 6: Calculate the value of sales by period and level of detail.

Sales by Period & Detail =

{FIXED [Initial Date of the Period], [Detail] : SUM([Sales])}

It is important to highlight that the result of the LOD calculation gives us the value of the total sales of each period (annual, quarterly or annual) and the level of detail selected. It does not matter if the sales are made on an even day or on an odd day.

 Step 7: Get the value of sales to graph. It is calculated according to the option selected by the user.

Sales Axis =  IIF([Accumulated Sales?]=FALSE,

SUM([Sales by Period & Detail]), RUNNING_SUM(SUM([Sales by Period & Detail])))

 Step 8: Calculate the variable “path” to join the points in the correct order and modify the aggregation level of the new variable to “Average”.

Path =  INT(DATETRUNC([Type Period?], [Order Date])) + [Points]

This formula adds to the value of the initial date of the period a value of zero for the first point and the value of one for the second point. In such a way that the second point of each period will have the value of +1 with respect to the first point of the period.

And when including the value of the initial date, each point in the chart will have a higher value than the previous one, which will allow joining the points in the correct order.

Step 9: Select the line chart and move to the shelves of:

  • Columns ➔ the variable “Date Axis” and modify to “Exact Date”
  • Rows ➔ the variable “Sales Axis” and apply the Computing table calculation option by “Date Axis”
  • Color ➔ the variable “Detail”
  • Detail ➔ the variable “Points”
  • Path ➔ the variable “AVG (Path)”

 Step 10: Adjust colors and formats. Ready!

Link Tableau Public

Picture0

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

Regards!

Rosario Gauna

Advertisements