Making a Pacing Dashboard


#1

For most companies, the consistent tracking of progress toward an objective can be key to making wise forecasting decisions. For example, an effective Director of Sales tracks and understands how his team is progressing toward their quota throughout a quarter;armed with that knowledge, he can adjust his forecast accordingly for the subsequent quarter.

A great tool to accomplish this is a Pacing chart, which displays two key points of data:

Your current/actual progress toward a time-based objective
The hypothetical progress you’d need to achieve that time-based objective

For example, here’s a bar chart which tracks how much New ARR a sales team needs to close to reach its quota this quarter.This team is doing fantastic:

Pacer Charts can come in many forms: bar chart, bullet chart, single values, or really any form you’d like.

The pacer data point (hypothetical progress) calculation can be as complex or simple as you’d like. It can be based on historic data (i.e. actual figures/progress from the same time last quarter), a complex curve, or just a simple linear progression.

In this example, we’re just going to focus on a simple linear progression and display the points in a bar chart.

##1. Setting the Goal

First we’re going to make an input where we can set and adjust our goal. On a fresh dashboard, make a control or hidden variable for setting the target value. Here we’re going to call it NEW_ARR_TARGET. It should be of Data type Number and you can set a default value.

Place it wherever you’d like and add some text to title it. It will look something like this:

##2. Chart Your Current Progress

Next make a chart with your actual metric for the current period. This example represents the total amount of won deals for the current month:

##3. Set up your Pacer

Next we’ll add a layer and put in the formula for our pace. Again, you can make this formula as complicated as you like, but we’re just going to map out equal progress each day of the month. The formula we’ll use will be this.

Day of the Month * TARGET / Days in the Month

You could do this in SQL, but Chartio also has some great relative date variables we can utilize as well. The ones we’ll use are {TODAY} and {CURRENT_MONTH.END}.

 SELECT {TODAY.PART('day')} * {NEW_ARR_TARGET}/{CURRENT_MONTH.END.PART('day')} 

To break this down here’s what just happened:

{TODAY.PART(‘day’)}: The current day of the month.
{NEW_ARR_TARGET}: The TARGET we’ve set with the dashboard drill control in step 1.
{CURRENT_MONTH.END.PART(‘day’)}: The day of the month of the last day of the month, or put another way: how many days there are in the current month.

Paste that SQL in for the second layer and make sure you change the Merge Type for the two layers to Union and choose Add Layer Names as shown in the following figure.

Now you should be able to show the results in a bar chart, with the two data points next to each other.

##4. Formatting

Finally, you’ll need to do a couple things to get the chart formatted well.

  1. Override the Y axis and set the top to be your goal. This will help you get a general feel for how close you are to the end target.
  2. Change the layer names to what you’d like the bars to be labeled. Here we’ve chosen “New ARR” and “Pacer”
  3. Save and arrange on your dashboard!


Salesforce Overview and You!
#2

Is there a way to count only weekdays while calculating the earning rates and doing projection forward?

I think the effect of counting weekends washes out if you divide your revenue by 7 days per week and then also project outwards using 7 days a week, but calculating the revenue per day you need to hit going forwards gets a little messed up if you’re not earning revenue on weekends.


#3

Hey Dan!

You can remove the weekends but it will require some modification to the calculation above. You can achieve this using layers:
layer 1: calculate ARR or revenue
In this layer you would need to include the day of week dimension so you can easily filter out the weekends and use the pipeline to calculate the total ARR or revenue after you’ve filtered out the weekends

layer 2: calculate pacer
Assuming you’re doing a monthly pace, you can use this query with any postgres datasource (chartio demo for example) to calculate out the pacer:

--generate dates for the month from the first day to the last day
with days as (
select * 
from generate_series({CURRENT_MONTH.START}::DATE,{CURRENT_MONTH.END}::DATE,'1 day')),

--associates each day with a day of week
dow as (
select days.generate_series, TO_CHAR(days.generate_series,'DD') as day, TO_CHAR(days.generate_series, 'D')::INT as day_of_week
from days
order by 1 ASC),

--calculate how many days in the month excluding the weekends
total as (select count(dow.generate_series) as total from dow where dow.day_of_week not in (1,7)),

--calculate how many days into the month
pacer as (select sum(case when dow.day::INT < {TODAY.PART('day')}::INT then 1 else 0 end) as pacer 
from dow 
cross join total),

--calculate the target goal
target as (CALCULATE TARGET as target),

--get the day of month, total, days into the month, and target
pace as (select dow.day, total.total, pacer.pacer, target.target
from dow
cross join total
cross join pacer
cross join target
)

--calculate the pacer
select pace.pacer*pace.target/pace.total as pacer
from pace
limit 1

the query generates a date series for the month and filters out all the weekends and does the calculation for the pacer.

You can then union all the layers together.

If you have any questions, don’t hesitate to reach out to support@chartio.com or via the in-app messaging system under the help menu!

Jen