Show Looker: Forecast targets for any metric using Custom Calculations

Looker includes reference lines, but these aren’t a great way of visualizing a target that occurs in the future.

For example, let’s use the learn.looker.com dataset and try to vizualize a Q1 Target:

This isn’t great for two reasons:

  1. Reference Lines don’t show a growth gradient
  2. We can’t project data any further into the future than our most recent data-point in the dataset. In this case, Jan 18th.

Here’s a workaround that solves both of these problems using some SUPER HACKY custom table calculations.

We’ll solve point 1 by adding a calculated line between the start and end values at the start and end of the quarter.

We could solve point 2 by joining our data to a ‘Calendar dates’ table that includes dates that are in the future. However, this would need to be joined to every explore that you are trying to forecast, which could add up to lots of model bloat.

Instead we’ll solve point 2 by adding a future date value onto the table using table calculations. Since we can’t simply append a table row, we’ll need to sacrificing a row from the table and modify the date value. (I told you this was super hacky.)

Step 1:
Create a dummy custom table calculation: first_date
diff_days(${orders.created_week},now())=max(diff_days(${orders.created_week},now()))
This simply identifies the earliest row in the table, that we can ‘sacrifice’ to turn into the End of Quarter date value.

Step 2:
Create a replacement date column: dummy_date
if(${first_date},date(2016,04,01),${orders.created_week})
This is the new date column we will use, hiding the original ‘created week’ dimension from the visualization. Now’s a great time to also hide the ‘first_date’ column from the visualization, too.

Step 3:
Create a replacement ‘actuals’ column: actual
if(${first_date},null,${orders.count})
Since we’re taking away one of the date values to use with targets, we need to replace the old value that happened on that date with ‘null’. Hide the ‘Orders Count’ measure from the visualization.

Step 4:
Create a target column: target
if(${first_date},450,null)
So, we actually need this to be a measure since we are plotting it on the y-axis, but it will default to being a dimension. We can hack it to become a dimension by including another measure in the calculation like so:
if(${first_date},450+${orders.count}*0,null)

Step 5:
Hardcode the start value for the target, by wrapping the target calculation in another ‘if’ statement:
if(${orders.created_week}=date(2016,01,04),234, if(${first_date},450+${orders.count}*0,null) )
This makes sure there is a start date/value pair, as well as an end date/value pair for the target line.

Now turn off ‘plot null values’, and double check that you have hidden every column from the visualization except for ‘dummy_date’, ‘actual’, and ‘target’.

The End Result, after some minor reformatting:

Now it’s much easier to see how our current performance is measuring up against our quarterly target! Let me know if you find an easier way to do this :smiley:

6 Likes

For step 5, if you do this instead:

if(${first_date},
    if(${first_date},450+${orders.count}*0,null), ${orders.count})

Your target line will actually track the actual line, so there’s no need for hiding NULL values which allows area/stacked graphs

1 Like

Wow, how did I not see this earlier? Good stuff!

@brayden: You are a badass.

This is a ridiculously ugly hack! Respect, sir…

I would love to see Looker add something more formal to support this use case. The simplest version in my mind would be a reference line that has a start and end y-axis value. Simple linear-sloped reference line. That would solve 80% of my forecasting needs. For more complicated ones, I’m likely to just create a dedicated “goals” table and join against it.

But my frustration over lack of sloping reference lines dissolved when I saw this righteous monstrosity.

What I did was code a goal that we are trying to reach by the end of the month and we can see whether we are “above” or “below” where we need to be:

1 Like

Looker’s new “Zero Fill Dates” feature may simplify this ugly pattern down somewhat. If anyone has a newer example of implementing future targets, I’d love to see!

2 Likes

hey Brayden, i am not able to add future date to the table
(sorry but i am not able to gather how i can achieve this by using the step 2!) .
Could you please assist thank you :slight_smile:

I didn’t use the sample data, but I’m assuming $(orders.count) is a lifetime total? Our data is based on the day they happen (i.e. 5 orders on Tuesday + 7 orders on Wednesday = 12 orders total). So I ended up having to add running_total to Step 3 and Step 5

Step 3 (Actuals)
if(${first_date},null,running_total(${orders.count})+sum(${first_date_sum}))

  • I had to create an extra column for ${first_date_sum} since orders.count is null when ${first_date}… i.e. my running total was under-counted by the number of orders that happen on the first date
  • Since this is a running total, you’ll need to sort your table ascending by dummy_date

Step 5 (Target)
if(${first_date}, if(${first_date},${q4_target}+${orders.count}*0,null), running_total(${orders.count})+sum(${first_date_sum}))

  • I added a calculated field for ${q4_target} with IF statements depending on a value in a separate column, so that I can filter the report

And just to go next-level, I added a simple forecast

Step 6 (Forecast)
if(${actual}>0,${actual}, sum(${orders.count})+sum(${orders.count})/count(${orders.created_date})*diff_days(now(),date(2019,12,31)))

  • The instructions (and perhaps the sample data) used a Created_Week, but I am using days in my report
  • This formula is able to calculate an average, because each days’ Orders are distinct to that day; if the sample data is based on a lifetime total, this won’t work
  • This formula may be over/under counting the amount of days remaining in the Quarter, so if anyone has a better way to forecast please share!

The biggest difference between our two approaches is that in the Original example, the Actual and Target lines can have different values for the same day… whereas my approach means that all three lines (Actual, Target, Forecast) will have the same data for previous dates, but will change when looking at future dates

1 Like