Trend Lines and Reference Lines In Tableau

We often used to compare things with each other to find out the best insights to choose one of the things. It is the best way to get valuable information and a great understanding of the products. For example, a Comparison between Tableau and IBM Cognos, Understanding the difference between TCP and UDP, so on. Likewise, the trend lines in Tableau is used to estimate the relationship between the variables. So What is the Trend line in Tableau?

What is the Trend Line in Tableau

The Trend lines in Tableau are used to estimate the trend of the variables. It helps to identify the relation between the two variables by observing the trend of the variables simultaneously. For example, a trend line for sales data to determine insights, whether sales are increasing or decreasing at a particular time frame. 

This way, the trend lines in Tableau helps to interpret the data trends, estimate the future scenarios, and define the relationship between the variables in the analysis.

Want to become an expert in tableau? then enroll in "Tableau Certification Training" now!

Trend Line Models

In Tableau, there are various types of trend lines classified depending on the model. They are as follows:

#1. Linear Trend Line

Linear trend lines are the best way to estimate a linear relationship in the data. The formula for the linear trend lines is as follows:

Y= b0+b1*X

Where X is an explanatory variable, Y is the response variable, b0 is the intercept of the line, and b1 is the slope.

The Linear trend line represents the simplest trend model estimating the relationship between the variables of whether b1 is increasing or decreasing at a steady rate and thus resembles a linear pattern.

#2. Logarithmic Trend Line

Logarithmic trend lines are used, when the rate of change between the variables increases or decreases rapidly. The formula for Logarithmic trend lines is as follows:

Y= b0+b1*ln(X)

Here, ln(X) is the natural logarithm of X.

Since this quantity does not define negative values of X, any negative values of X have to be filtered before the trend line is analyzed. Logarithmic trend lines should be avoided in a case where a considerable set of marks includes negative values for a field in the column itself. Trend lines will create a report regarding the number of marks that are filtered before the estimation of the model.

#3. Exponential

In the exponential Trend line model, a response variable is converted by a natural logarithm before model estimation. The formula for the exponential trend line is as follows:

Y= exp(b0)* exp(b1*X)

So, the values were plotted in the view are established by connecting several explanatory values is used to discover the value of ln(Y).

ln(Y)= b0 + b1 * X

Then, the values were plotted. The exponential trend line model displays the following formula:

Y= b2 * exp(b1*X)

Here, b2 represents the value of exp(b0). And no negative values are defined in this exponential trend line.

#4. Power Trend Line

Power trend lines are the curved lines that are used when the dependent variables increase at a predefined rate b1. The formula for power trend lines are as follows:

Y= b0*X^b1

By the power model, the two variables are converted by a natural logarithm, before model estimation developing in the formula below:

ln(Y)= ln(b0) + b1 + ln(X)

Then, the values are plotted in the trend lines. And no negative values are defined in this trend line. Those values that are less than 0 have to be filtered before the estimation of the model.

#5. Polynomial Trend Line

Polynomial trend lines are the curved lines that are used when dealing with variables that have fluctuating relations between variables. The formula for the Polynomial trend line is as follows:

Y= b0+b1*X+b2*X^2+b3*X^3+...

Select the degree that lies between 2-8. When the polynomial degrees are high, it inflates the comparison between the data values. If the data increases quickly, the lower-order terms might have no variation when compared to higher-order terms, and it is also difficult to estimate the results accurately. 

Read these latest Tableau Interview Questions and Answers that help you grab high-paying jobs

 

Trend Line Model Terminology

There are several values that appear when we see the relationship for the Trend line model. The below table describes all the terms as follows:

Terminology	Description
Model formula	The model formula reflects on excluding factors from the model.
Number of modeled observations	It defines the no. of rows that are present in the view.
Number of filtered observations	It defines the no. of observations that are eliminated from the Trend line model.
Model degrees of freedom	It defines the no. of parameters is required to complete the model. The trend line model, such as Linear, exponential, and logarithmic trends, have a model degree of freedom II. The Polynomial trends include the model degree of freedom1 plus a degree of a polynomial.
Residual degree of freedom	It defines the no. of observations subtracts from the no. of parameters predicted in the model for a fixed model.
MSE (mean squared error)	It is the quantity of SSE that is divided by its degree of freedom.
SSE (sum squared error)	It is the difference between the predicted value and the observed value of the model. In ANOVA, the difference between the model and SSE of the model in that row.
Standard error	It defines the square root of the MSE of the full model. An estimate of the standard variability of the random error in the model formula.
R-Squared	It is the value of how the data fit the linear model. R-squared is the ratio of the variance of the unexplained variance, model’s error to the total variance of data. 1-i=1n(yi-yi)2  / i=1n(yi-y)2 When y-intercept is enforced to 0, R-squared is obtained using the below equation.1-i=1n(yi-yi)2  / i=1nyi2
p-value	It defines the probability of F random variable with the degrees of freedom exceeds the F in a row of the analysis.
Analysis of Variance	It is the list of information for each factor in the trend model line. It is also known as ANOVA.
Value	The estimated value of the coefficient for the term.
p-value	The probability of observing the t-value that is larger in the magnitude of the true value of the coefficient is zero. So, a p-value of 0.05 gives 95% confidence that the true value is not zero.
StdErr	This error shrinks due to the quantity and quality of the information is used within the estimate growth.
t-value	The statistic used to test the null hypothesis that the true value of the coefficient is zero.

 

Do you know-- Top OpenSource Data visualization Tools

How to Add the Trend Lines in Tableau?

When we add trend lines to a view, you can specify how we want to display them to present attractively. To add a trend line to a visualization, do the following steps:

1. Click the Analytics pane. Drag the Trend Line in the list and drop it into one of the Trend line models.

2. After selecting the linear trend line, the trend lines appear as per the data. Drag and drop the required Dimension into the Worksheet to have a clear data analysis.

3.  Each trend line can be color-coded to the data it represents perfect visualization.

We can not add the Trend line to a view that the Product Category dimension, which contains strings, is on the columns shelf and the profit measure on the row shelf. However, you can add a trend line to view the data over time

How to Edit the Trend Lines in Tableau?

Now, let’s see how to edit the trend line in Tableau. Tableau allows us to edit the added trend line to fit with the analysis.

To edit the trend, do the following:

1. Right-click a trend line in the workspace, and Click Trend Lines, and then select ‘Edit Trend Lines’ in the list as shown in the figure.

2. A ‘Trend Lines Options’ window appears, In that, we can configure the following options to edit the Trend Lines.

• Model type: It allows for editing the model type of the selected trend line.
• Include the following fields as factors: It enables us to choose the fields to use factors in the trend line model for perfect data visualization.
• Allow a trend line per color: It allows encoding color for the selected trend line in the view. 
• Show Confidence Bands: The Confidence bands show upper and lower 95% confidence lines by default when you add trend lines. This option is not supported for the Exponential Trend line model.
• Force y-intercept to zero: It is used when we want the trend line to begin at zero. It enables only when both the Rows and Columns shelves contain continuous fields, as with a scatterplot.
• Show recalculated line for highlighted or selected data points: It is used when we choose or highlight the data in the visualization.

3. Using all the options, we can edit the trend lines as per the requirement to make the data for clear representation.

How to Remove the Trend Line in Tableau?

To remove a trend line from a visualization, drag it off the visualization area. To remove a particular trend, right-click at the Trend line and select the Remove option.

To remove the entire model of trend lines, Click Analytics, Select Trend lines and then disable the Show Trend Lines option.

Access Trend Line Significance

To view the significant information of any specific trend line in the worksheet, float the cursor to the trend line. It shows the details of the trend line, as shown in the figure.

 

The first line represents the ‘Profit’ value and the ‘Year of Order Date’ as per the data given in the trend line.

The next line gives an R-Squared value; it is the ratio of variance as defined by the model, to variance within the data.

The Last line represents a P-value that is the probability of that equation present in the first line provides the random chance. If the p-value is small, then the model is more significant—the p-value with 0.05 or less than is usually considered as adequate.

Significance of Entire Model

The trend line is required to have a good model, that is the value of the quality of the model’s expectations. Also, we focus on the importance of every factor of the model. The following step provides a way to view these values. 

• Right-click in the worksheet, click ‘Trend Lines’, and select ‘Describe Trend Model’ in the list.

When executing the significance, the smaller p-value gives the more significance of the model. It can find the model which has a statistical significance yet that includes a single trend line but does not provide a complete significance.

When a p-value is less, it represents the variation in an unexplained variance of models with or without relevant measure is the result of a random possibility. This p-value compares the entire model fit to the model fit determines the average of the data. It accesses the explanatory trend line model of a quantitative term in the formula, which can be Linear, Logarithmic, Polynomial, or Exponential with the fixed factor. We can access significance by using a ‘95% confidence’ method.

Significance of Category Factors

With the use of an ANOVA table, every field is termed as a factor in the trend line model that is listed. In the given figure, a p-value represents how the field is added to the significance of an entire model. The values displayed in each field are defined and is compared with a model that is not included in the question.

For the ANOVA model, the trend lines were described using the mathematical form:

Y= factor 1 * factor 2 * ....factor N * f(X) + e

Here, Y is the response variable that corresponds to a predicted value. X is an explanatory variable, and e is a random error. And the * is a specific matrix that corresponds to the category fields works as a multiplication operator to make the two matrices with the same no. of rows and returns the new matrix. For example, if factor1 and factor2 have three variables, then nine variables are produced in the trend line model formula.

Trend Line Assumptions

When a trend line is created in Tableau, the calculations are based on certain assumptions. Every trend line is created by having some computations that depend upon the following Trend line assumptions:

• The model is a valid functional calculation of data that generates the process.
• The error in the model is average-zero and unassociated with the independent variable.
• The errors should have constant variance and are uncorrelated with one another.
• The explanatory variables are not the exact linear variables of one another.

Conclusion

On wrapping up, the trend lines will be the best fit in advance with a few clicks and make the decisions more simple with no scripts and no coding required. In this article, we have learned all the insights of the Trend Lines in Tableau such as How to add, edit, and remove the trend line along with the terminologies used in the Trend Lines, different types of Trend Lines model, Significance of the model, and few assumptions to create a trend line. I hope you find relevant information.