Predictive Modelling Functions in Time Series Visualisations

This document describes the steps required to extend a time axis into the future, so that you can make predictions for future dates using predictive modelling functions.

To see how, follow along with the below example.

Step 1: Create a visualisation

  1. In Tableau Desktop, connect to the Sample - Superstore saved data source, which comes with Tableau.

  2. Navigate to the Sheet 1 worksheet.

  3. From the Data pane, drag the Order Date dimension to the Columns shelf.

  4. Open the measure’s context menu to change its list level to Month and Year:

    measure context menu

  5. Drag Profit to the Rows shelf.

Step 2: Create a prediction calculation

  1. Click to open the Analysis menu at the top, and then select Create Calculated Field.

  2. In the Calculation Editor, do the following:

    • Name the calculation: Quantile Predict Median Profit.

    • Enter the following formula:

    • MODEL_QUANTILE(0.5, SUM([Profit]),ATTR(DATETRUNC('month', [Order Date])))

    The MODEL_QUANTILE function takes a given quantile and predicts values based on the predictors you input. In this case, the quantile = 0.5, which predicts the median. We want to predict profit, so the target expression is SUM([Profit]). And since we want to base the prediction on past performance, we need to include date as a predictor.

    Note: With Predictive Modelling functions, you cannot mix aggregate and non-aggregate arguments. Since the target expression must be an aggregate, so must the predictor. In this case, we used ATTR(DATETRUNC('month', [Order Date])). For more information about the use of ATTR functions, see When to Use the Attribute (ATTR) Function.
  3. When finished, click OK.

    The prediction calculation is now added as a calculated field in the Data pane.

Step 3: Add the prediction calculation to the view

  1. Drag the prediction calculation to the Rows shelf, to the right of SUM(Profit).

  2. Right-click (Control-click on Mac) the measure and select Dual Axis.

  3. To align the two axes in a dual axes chart to use the same scale, right-click (Control-click on Mac) the secondary axis, in this case Quantile Predict Median Profit, and select Synchronise Axis. This aligns the scale of the two axes.

    line chart with profit ine and prediction curve

Step 4: Extend the date range and densify the data

The final step is to extend the horizontal axis into the future so that you can add marks beyond the current date range.

  1. To do this, open the context menu of the MONTH(Order Date) pill, and select Extend Date Range.

  2. In the Extend Date Range dialog, let’s extend the axis by 16 months:

    Extend Date Range dialog

    Tableau has automatically updated the view by extending the horizontal axis and includes predictions on these future dates:

    line chart with date axis extended into the future

    Notice that the blue Profit line doesn’t extend to the end of the graph. However, the orange line representing the prediction calculation, Quantile Predict Median Profit, does extend to the end of the graph. Computing the predicted profit on future dates is an example of data densification, which occurs when Tableau creates additional marks in the view to compensate for missing values. Data is not added to the underlying data source; marks are simply generated and added to the view.

Note: When you extend a date range into the future, Tableau automatically enables the Infer Properties from Missing Values option in the Analysis menu. This ensures that your prediction calculation will work on the missing values. For more details on this menu option, see Predictive Modelling with Generated Marks.

For more information on data densification, see this data densification post(Link opens in a new window) from Data Plus Science(Link opens in a new window).
Thanks for your feedback!Your feedback has been successfully submitted. Thank you!