Introduction

I previously wrote about the need for better documentation on Tableau linear trend models.  At the time, I was not able to examine the other types of trend models offered by Tableau, which include linear, logarithmic, exponential, and polynomial. This weekend I remembered to get back to the subject, which has lead to content of this post.  Although I wouldn’t call this the definitive treatise on the topic of Tableau trend models, I know that it will help other Tableau users that struggle to understand the output generated by the Tableau trend models.  Additionally, this work identified one possible error in the Tableau trend model output.

If you like this article and would like to see more of what I write, please subscribe to my blog by taking 5 seconds to enter your email address below. It is free and it motivates me to continue writing, so thanks!

Method

In Excel, I generated 12 mathematical models that have exact analytical solutions to produce the data for examination in Tableau.  Click here to retrieve the Excel file that contains these math models. Figure 1 shows the two linear models generated – one for increasing and one for decreasing linear data.  There is no difference between the models except for the sign of the slope.  I only included the decreasing model to keep symmetry relative to the other five sets of models shown below.

Figure 1 – Two linear models used as examples, one increasing and one decreasing.

Figure 2 shows two logarithmic models generated, including one in base e and one in base 10.

Figure 2 – Two logarithmic models used as examples, one in base e and one in base 10.

Figure 3 shows two exponential models generated in base e, one for exponential growth and one for exponential decay.

Figure 3 – Two exponential models in base e, one for growth and one for decay.

Figure 4 shows two exponential models generated in base 10, one for exponential growth and one for exponential decay.

Figure 4 – Two exponential models in base 10, one for growth and one for decay.

Figure 5 shows two polynomial models generated for degree 2 and 3.

Figure 5 – Two polynomial models for degrees 2 and 3.

Figure 6 shows two polynomial models generated for degree 4 and 5.

Figure 6 – Two polynomial models for degree 4 and 5.

Results and Interpretation

The data created by these 12 models were loaded into Tableau and trend lines were created for each case.  A total of 12 dashboards like the one shown in Figure 7 were created, one dashboard for each mathematical model.  The Tableau public workbook containing these 12 dashboards can be downloaded by clicking here.  The twelve tabs along the top of the page each represent a model (i.e., Linear_Increasing_DB, Linear_Decreasing_DB, etc. can be viewed using the slider provided). The trend line descriptions were captured and pasted onto each dashboard (as an image) along with another image of the mathematical model description. The example shown in Figure 7 is just for the linear increasing model and I’ve chosen to not show the other 11 graphics in this post but are available by clicking here to download a pdf file that shows them all.

Figure 7 – The linear increasing model in Tableau, with the trend model description shown along with the mathematical model that generated the data. This is the first of 12 dashboards in the Tableau workbook for this work.

These 12 dashboards are not completely interactive because there is no way for me to get the trend model annotations (i.e., the graphical image shown on each dashboard that describes the Tableau trend model output) to be updated dynamically.  This is a Tableau deficiency that I have written about a few times in this blog series.  If you want to play with the models by changing the coefficients, you can do that in on the “Model Descriptions” page in the Excel workbook.  The small charts next to each model will change in Excel as you change the input parameters and the data sent to Tableau will also be updated if you connect your dashboards back to the Excel worksheet. Just remember that the Tableau trend model descriptions shown in figure 7 are just images of the original model presented in this paper and will not change with any new coefficients that you investigate.

You might be asking yourself the following question. If these dashboards are not completely interactive, what good are they?  Well, the topic at hand is to gain a better understanding of how Tableau reports the results of the trend models. To understand this, you need to look carefully at the content of each of the twelve dashboards.  Click here to download all the math models in a 12-page pdf file for easy printing.  The mathematical model is presented for each of the 12 cases, as are the model input parameters that created the data.   The Tableau curve fits are presented, with the reported model coefficients given. For the example in Figure 7, the model slope is presented as 0.34 and the equivalent Tableau trend model term is called the X-value coefficient term (also = 0.34).  The model formula shown in Tableau is X-value + intercept, which is less than satisfactory in my opinion (see my previous post regarding this nomenclature). By comparing the stated mathematical model formula and its coefficients to the stated Tableau  model and its coefficients, you can go back and forth from the math model to the Tableau trend model output without any further guess work.  

What do I mean by guess work?  If you think you are a Tableau master, answer the following questions.  The Tableau logarithmic and exponential trend models uses (1) e as a base or (2) 10 as a base?  If you want results in base 10, what do you have to do to the calculated Tableau coefficients?  Do you believe the value of the intercept for Tableau reported exponential models?  Can you figure out how to use the Tableau trend line models given the model descriptions? For answers to these questions, I’ve created Table 1 (first 8 models) and Table 2 (4 polynomial models)  to present the results of this work as clearly as I can.  The standard math models are shown in columns 1 and 2 and the Tableau model equivalents are shown in columns 3 and 4.   Curve fitting results are shown in column 5 and notes to explain any observed differences are shown in column 6.

Table 1 – Comparison of Standard Math Model to Tableau Trend Lines for First Eight Models.

Table 2 – Comparison of Standard Math Model to Tableau Trend Lines for the Four Polynomial Models (click for full size image).

Conclusions

Tableau does a great job in curve fitting.  The examples I created here have exact analytical solutions, so I would expect that each model would produce r-squared values of 1.0. This is achieved by Tableau. The polynomial coefficients show slight numerical errors (I presume round-off error) but this is understood quite readily.  The two biggest problems identified are: (1) the intercepts for the four exponential models appear to be incorrect and (2) Tableau does a poor job of explaining the model formulas in each of their trend models.  There really is no excuse for this and Tableau really needs to correct this part of their code.  At this time, I have no explanation as to why the Tableau generated intercepts in the exponential models are not in agreement with the correct solution.

Update 10/18/13

I resolved the number (1) issue above.  For a complete explanation, click here to read the post.