How to Forecast in Looker with Table Calculations

[sam8]

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

Starting in Looker 21.14, analysts can add data projections to new or existing Explore queries to help users predict and monitor specific data points with the Forecasting Labs feature.
 

The Problem: I Need to See the Future!
 

I have some data from the past, and I want to use it to predict the future. Trend lines are partially useful, but they don't extend past the current data.

This Explore is what I currently have:

This Explore is my goal:

A Solution: Linear Forecasting with Table Calculations

We can achieve this kind of forecasting by using table calculations instead of trend lines. At a high level, our two steps are to create future dates and then to calculate the line equation with table calculations.
 

Step 1: Creating Future Dates with Dimension Fill

We need our graph to show both existing and future dates along the x-axis. The easiest way to achieve this is to turn on dimension fill and change the filters to the range you would like to see, as was done in the Explore shown below.

If you cannot use dimension fill for some reason, you will need to right-join your explore with a calendar table containing future dates.
 

Step 2: Calculating the Line Equation with Table Calculations

Now we can create the line equation with table calculations. Fortunately Looker has the slope and intercept table calculation functions to help us with that! The only catch is that these functions expect integers and not dates. Here are the steps to take:

1. First we need to convert the date to an integer. We can do this with a simple table calculation like this:

   diff_days(${orders.created_week},now())

   You can use any timeframe or computation as long as your end result is an integer that increases as the date increases.

   

2. Now let's use Looker's built-in slope function. The new day index will be our "x column" and the measure (here orders.count) will be our "y column":

   slope(${orders.count}, ${day_index})

3. Similarly, let's create the intercept:

   intercept(${orders.count}, ${day_index})

4. Now hide the day_index, slope, and intercept calculations from the visualization. We only need these to create the final calculation.

   

5. Finally, it's time to create the line equation. You may remember "y=mx+b" from school — let's put it to use!

   ${slope} * ${day_index} + ${intercept}

6. Your finished product should look like this!

   

   
    

Can I Use Exponential Regression instead of Linear Regression?

Yes, absolutely! You can follow the same steps, but slightly change the table calcs as follows:

• Slope: slope(ln(${orders.count}), ${day_index})
• Intercept: intercept(ln(${orders.count}), ${day_index})
• Equation: exp(${intercept} + ${slope} * ${day_index})