Interacting with your trend line in Tableau (Part 2)

Data

Interacting with your trend line in Tableau (Part 2)

by Lucie Krennwallner

PART 2 In part one we familiarised with Tableau’s automatic Trend Line, information provided and inferences possible. Now I want to focus on the fun part: how to calculate the trend line ourselves using Tableau-R integration as well as two applied examples “How much above/below ?” and “What if”. HOW TO CALCULATE A LINEAR TREND LINE USING TABLEAU & R INTEGRATION? Tableau provides (at least) two main options to calculate a trend line: either you could use Tableau’s R integration or calculated fields.

  1. TABLEAU-R-INTEGRATION

If you know a bit of R, then you’re spot on – Tableau R integration saves you writing complicated Tableau calculations. Connect to R [1] and create the following calculated field ”R Trend Line”:          

SCRIPT_REAL(

” tl <- lm( .arg2 ~ .arg1)

tl$fitted ” ,

          SUM([x]), SUM([y]))

A note for Tableau-R beginners: the equation

          lm( .arg2 ~ .arg1)

calculates all parts of the Trend Line (slope, intercept, estimated values). Because Tableau can only retrieve a single column from R (but no matrix or table), we need to extract the estimated values to be displayed in the dual axis chart by using „tl$fitted“. Now include the calculated trend line into the chart. To do so, drop “R Trend Line” onto the row shelf – don’t worry if the chart is empty at first! Right click on the Trend Line calculation and adjust ‘Compute Using’ to ID (see Figure 8). Then choose ‘Dual Axis’ – and you’re nearly done. The calculated and the automatic trend line do not lie on top of each other quite yet. It needs Axis Synchronisation – only Tableau does not allow this: this option is greyed out.

 Fig 9 R Trendline

Figure 9: Interactive Menu ‘Axis synchronisation’ Why is this? The reason is simple: if data types of the two axes differ, Tableau cannot synchronise them. Thus convert the Trend Line with the   INT() function:

INT(

SCRIPT_REAL(

” tl <- lm( .arg2 ~ .arg1)

tl$fitted ” ,

          SUM([x]), SUM([y]))

)

And – voila! –  there is the synchronisation function back. Now the Trend Lines matches. Note: the above calculation can be written in a single line, of course; the spacious presentation is only a way to make it easier for you to understand Tableau spelling. However, if your plot looks still strange, because your R Trend Line appears as a dotted line although you have chosen ‘Line’ in the marks card, then remove > Measure Names < (see Fig. 10, red circle) from the Trend Line card.

Fig 10 two dots plot

Figure 10: remove >Measure Names< from marks card to see R Trend Line as a line.

PRACTICAL EXAMPLES

Example 1: “How much above the Line?”

What do you do if you want to quantify and visualise the deviation of an observation (data point)  from the Trend Line?  That’s easy, now; right? Just calculate “How much above the Line?” in a calculated Field using

SUM([y]) – [Trend Line].

Remember: the y-axis in a scatter plot is SUM([y]) and NOT just y. When you have the trend line on the row shelf with ‘Compute Using’ on ID, drop the calculation “How much above the Line?” on to ‘Size’ on the marks card (and if you want to see the label, drop a copy on to ‘Text’ too). Then create a dual axis chart and choose from the marks card Gantt Bar for the trend line to visualise the deviation from the trend line (Fig.11).

Fig 11 How much abvove below

Figure 11: Visualising distance of observation from the trend line

Example 2: “What-If?”

Let’s imagine you would like to analyse the profitability of customers. When your company acquires a new client we’d like to evaluate what to expect based on the currently known trend. For this ‘what-if’ scenario we need to create an option for the analyst to choose a value within the data set or SLIGHTLY outside (if you want to do go far beyond your own data set, better use a different method of predictive analytics).

As a reminder to calculate the trend line we used the formula

LineFormula

So to answer your question we need to take three steps:

a) extract the constants

Intercept:   SCRIPT_REAL( “lm( .arg2 ~ .arg1) $coefficients[1]” ,  SUM([x]), SUM([y]))

Slope:         SCRIPT_REAL( “lm( .arg2 ~ .arg1) $coefficients[2]” ,  SUM([x]), SUM([y]))

b) we have to replace our known x-values from the data set with values the analyst can choose from. To do so create a parameter (Fig. 12) , which is then called in the trend line calculation replacing  SUM([x]) with SUM( [Parameter]).

Fig 12 What if x Value Parameter

Figure 12: Create Parameter “What-If x-Value”

c) Finally, we want to see only ONE estimated value, not all the possible values. In other words we need to filter the returned y-values and the easiest way is to use the function  First().

Piecing these three steps together the new “R What-If Trend Line (What if)” calculation reads then like

If FIRST() = 0 Then INT(( [R slope] * [x(what if)] ) + [R intercept] ) END.

Fig 13 R What If

Figure 13: What-If Analysis using R in Tableau

HAPPY ANALYSING!

[1] http://www.tableau.com/wp-content/uploads/sites/default/files/media/using-r-and-tableau.pdf

More About the Author

Lucie Krennwallner

Interacting with your trend line in Tableau (Part 2) PART 2 In part one we familiarised with Tableau’s automatic Trend Line, information provided and inferences possible. Now I want ...
Interacting with your Trend Line in Tableau (Part 1) Hot topics in the world of Big Data are forecast and trend models, and Tableau has integrated both features. All it takes are two ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072