Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 35 ยท Advanced Analytics

Trend Lines and Forecasting in Tableau

Add trend lines and forecasts in Tableau, read the model options and confidence bands, and reproduce both calculations with pandas so you can trust them.

A line chart shows what happened. The Analytics pane in Tableau helps you answer two follow-up questions: "Is there a direction in this noise?" and "Where is it heading next?" Trend lines answer the first question, forecasts answer the second. Both are one drag-and-drop away, which is exactly why they are easy to misuse. This lesson shows how each one works, what the options mean, and when you should not trust the result.

Tableau Desktop is a graphical program and cannot run inside a web page, so the steps below are written as instructions you follow in Tableau. The runnable Python examples are stand-ins: they reproduce the arithmetic with pandas and NumPy on a small data set so you can see the numbers behind the picture. They are not Tableau's own code, and where Tableau does something more elaborate (for example choosing model parameters automatically), the lesson says so.

The Analytics pane

On the left of a worksheet there are two tabs: Data and Analytics. The Analytics tab holds three groups of items:

  • Summarize: constant line, average line, median, box plot, totals.
  • Model: average line with 95% confidence interval, trend line, forecast, cluster.
  • Custom: reference line, band, distribution, box plot.

You drag an item onto the view and a small drop zone appears; for a trend line the zones are the possible models, for a forecast there is a single "Forecast" zone. Items only light up when the view can support them. A forecast, for example, needs a continuous or discrete date on Columns and at least a few time periods of data.

Two questions, two tools

Trend line A fitted line through the marks you can see.
Forecast Future periods estimated from the past.
1 / 2

Trend lines

A trend line is a statistical model fitted to the marks in the view. Build a line chart of monthly sales (drag Order Date to Columns as a continuous month, Sales to Rows), then drag Trend Line onto it. Tableau offers five models:

Model Shape Typical use
Linear straight line steady growth or decline
Logarithmic fast rise that levels off diminishing returns
Exponential accelerating curve compounding growth
Polynomial curve with bends (you pick the degree) cyclical shapes
Power curve through the origin scaling relationships

Right-click the line and choose Describe Trend Model to see the formula, the R-squared and the p-value. R-squared tells you how much of the variation the line explains (0 to 1). The p-value tells you how likely it is to see a relationship this strong by chance if there were no real trend. A tiny p-value with a tiny R-squared means "there is a trend, but it explains very little".

Two options matter a lot:

  • Show confidence bands: a shaded envelope around the line. The wider it is, the less certain the fit.
  • Allow a trend line per color: if you put a dimension on Color, you get one line per color. Turn it off for a single overall line.

A trend line is computed from the marks that are in the view. Filter out a year and the line changes, because the model is refit.

Reproducing a linear trend line

A linear trend is ordinary least squares: find the slope and intercept that minimize the squared distance to the points. NumPy does that in one call. The data below is 24 months of sales for a small shop.

Python Linear trend line stand-in

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Compare this with Describe Trend Model in Tableau on the same series: the coefficients and R-squared match, because both solve the same least-squares problem. This is also a handy habit. When a trend number looks surprising, recompute it in pandas.

Forecasting

Drag Forecast onto a time-series view, or right-click the view and choose Forecast > Show Forecast. Tableau extends the line into the future and draws a shaded prediction interval (by default 95%) around it.

Under the hood Tableau uses exponential smoothing. The idea: the forecast is a weighted average of the past, where recent points count more than old ones. Tableau tries a family of such models:

  • None trend, none seasonality: just a smoothed level.
  • Additive or multiplicative trend: the level also drifts up or down.
  • Additive or multiplicative seasonality: a repeating pattern, such as a December spike.

With Automatic model selection Tableau fits the candidates and keeps the one with the best quality score; the parameters (how fast the weights decay) are optimized for you. In Forecast Options you can override this: set the forecast length (for example "next 6 months"), the aggregation (month, quarter), whether to ignore the last N periods (useful when the latest month is incomplete), and the model type.

Always set Ignore last 1 period (or more) when the final month is still in progress. A half-finished month looks like a collapse in sales and drags the forecast down.

A hand-built exponential smoothing forecast

Tableau optimizes its parameters automatically; the stand-in below fixes them, which keeps the code short and the result reproducible. It implements additive Holt-Winters: a level, a trend and a 12-month seasonal pattern, then projects 6 months ahead.

Python Holt-Winters stand-in

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Look at the low95 and high95 columns: the band gets wider every month. That is the same shape as the shaded area in Tableau, and it is the most important part of a forecast. A single number such as "month 30 will be 91" is false precision; the honest statement is a range. (Tableau computes its intervals from the model's own error variance rather than from the quick square-root rule used here, so its band differs a little.)

Seasonality needs data. Tableau needs at least two full seasons to estimate a repeating pattern (for monthly data with a yearly cycle, 24 months), and it needs more for it to be reliable. With less data it will quietly fall back to a model without seasonality.

Forecast quality

Right-click the forecast and choose Describe Forecast. The Summary tab shows the forecast length and the model; the Models tab lists the quality metrics, among them RMSE, MAE, MASE, MAPE and AIC. In practice you can read them like this:

  • RMSE / MAE: typical error in the units of your measure; lower is better.
  • MAPE: error as a percentage of the actual value; handy for explaining accuracy to non-technical people.
  • MASE: error relative to a naive "same as last period" forecast; below 1 means the model beats the naive forecast, above 1 means it is not worth the trouble.
  • AIC: compares models for the same data; lower is better.

You can also Show Estimated Values to see the model's fit over the history, and Compare estimated and actual values to spot where it struggles.

When not to forecast

  • Too little history: five points are not a time series.
  • A structural change: a price rise, a new product line or a pandemic is not in the past data, so the model cannot know about it.
  • Irregular dates: gaps in the series produce poor fits. Tableau can fill gaps, but check what it assumes.
  • Aggregations that mix scales: forecast one measure at one level, not a ratio of two sums.

Forecasts also exist only where the view is a time series built on dates. Calculated fields can't be forecast if they use table calculations that depend on the view layout, and forecasting is not available on live connections to some multidimensional sources (such as cubes); extract the data instead.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which Tableau model does a linear trend line correspond to mathematically?

    A linear trend line fits slope and intercept by least squares, exactly what np.polyfit(x, y, 1) computes.
  2. 2. A forecast's prediction interval gets wider for periods further in the future. What does that tell you?

    Each extra step ahead adds error, so honest intervals widen. A flat band would be a warning sign.
  3. 3. The last month in your data is only half over. What should you set in Forecast Options?

    An incomplete period looks like a drop and pulls the forecast down; ignoring it keeps the model on complete data.
  4. 4. A MASE of 1.4 means:

    MASE compares errors with a naive forecast. Values above 1 mean the model does worse than the naive one.

Summary

  • Trend lines and forecasts live in the Analytics pane and are computed from the marks in the view.
  • Trend line models: linear, logarithmic, exponential, polynomial, power; read R-squared and p-value in Describe Trend Model.
  • Forecasts use exponential smoothing with optional trend and seasonality; the shaded band is the prediction interval and widens with the horizon.
  • Ignore incomplete periods, and never forecast with too little or structurally broken history.
  • The pandas stand-ins reproduce the arithmetic; Tableau additionally optimizes parameters and picks the model automatically.

Next: Reference Lines, Bands and Distributions