Create a pacing dashboard

For most companies, the consistent tracking of progress towards an objective can be key to making wise forecasting decisions. A 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

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 as simple as you’d like. It can be based on historical data (e.g., actual figures or progress from last quarter), a complex curve, or a linear progression.

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

• In Visual SQL
• In Data Explorer

In the Visual SQL interface

1. Create a Text Input where you can set or adjust the goal. From the sidebar on a dashboard, click Add Control > Text Input. Name it something useful (e.g., NEW_ARR_TARGET) and make sure the Data type is Number.

2. Next, create a chart where the first dataset is your actual metric for the current period. This example represents the total amount from won deals for that month.

   

3. Add a new Query to the chart and put in the formula for the pace. You could make this formula as complicated as you’d like, but we’re just going to map out equal progress each day of the month. We want to get the following calculation:

   (current day of the month) * TARGET / (# of days in the month)
   

   You could do this in SQL, but Chartio has some helpful Relative Date Variables and datetime functions you can leverage. We’ll use the {TODAY} and {CURRENT_MONTH.END} Relative Date Variables and the PART() datetime function.

   Toggle your second Query to SQL Mode and run the following SQL statement:

   select {TODAY.PART('day')} * {NEW_ARR_TARGET}/{CURRENT_MONTH.END.PART('day')} as "Pacer"
   

   

   • {TODAY.PART('day')}: The current day of the month

   • {NEW_ARR_TARGET}: The TARGET we set with the Text Input Control created in Step 1

   • {CURRENT_MONTH.END.PART('day')}: The last day of the month—to get how many days there are in the current month

   • as "Pacer": Names the resulting column “Pacer”

4. Click Merge Queries. By default, the Queries are merged with an Outer Join. Edit the Join Action and change the join type to Union and select include query names. Click Save to apply the changes.

   

5. As is, the Bar chart shows Query 1 and Query 2 as the bar labels. Use an Edit Column Action on the Layer column, select Custom for the formula type, then use the following CASE statement to change the values from Query 1 and Query 2 to New ARR and Pacer, respectively.

    case when "Layer" = 'Query 1' then 'New ARR' when "Layer" = 'Query 2' then 'Pacer' end
   

   

6. Click Settings above the chart preview to open the chart settings and customize your chart.

   a. Give the chart the title New ARR and Pacer.

   b. Go to the Axis tab and select Range override in the Y-Axis section. Set the maximum to the same value as your goal. This will help you get an idea of how close you are to your target.

   c. Click Done to save your chart settings.

   

7. Click Save Chart to save your chart and arrange it on your dashboard.

8. Now create another chart to show how many days are left in the month. Toggle to SQL Mode and use the following SQL statement:

    select {CURRENT_MONTH.END.PART('day')} - {TODAY.PART('day')}
   

   

9. Make this chart into a Single Value chart, give the chart the title Days left, then save it to your dashboard.

   

Now you’ve got the makings of a pacing dashboard with some great potential!

In the Data Explorer interface

1. Create a Text Input where you can set or adjust the goal. From the sidebar on a dashboard, click Add Control > Text Input. Name it something useful (e.g., NEW_ARR_TARGET) and make sure the Data type is Number.

2. Next, create a chart where the first dataset is your actual metric for the current period. This example represents the total amount from won deals for that month.

   

3. Add a new dataset to the chart and put in the formula for the pace. You could make this formula as complicated as you’d like, but we’re just going to map out equal progress each day of the month. We want to get the following calculation:

   (current day of the month) * TARGET / (# of days in the month)
   

   You could do this in SQL, but Chartio has some helpful Relative Date Variables and datetime functions you can leverage. We’ll use the {TODAY} and {CURRENT_MONTH.END} Relative Date Variables and the PART() datetime function.

   Toggle your second dataset to SQL Mode and run the following SQL statement:

   select {TODAY.PART('day')} * {NEW_ARR_TARGET}/{CURRENT_MONTH.END.PART('day')} as "Pacer"
   

   

   • {TODAY.PART('day')}: The current day of the month

   • {NEW_ARR_TARGET}: The TARGET we set with the Text Input Control created in Step 1

   • {CURRENT_MONTH.END.PART('day')}: The last day of the month—to get how many days there are in the current month

   • as "Pacer": Names the resulting column “Pacer”

4. Rename the first dataset you created in Step 2 to New ARR and the second dataset to Pacer. These will be used as the labels for the bars in your Bar chart.

5. Click the Merge Datasets step in the Data Pipeline to change the merge type to Union and also select Add Dataset Names. Click Apply & Close to apply your changes.

   

6. Click Settings above the chart preview to open the chart settings and customize your chart.

   Go to the Axis tab and select Range override in the Y-Axis section. Set the maximum to the same value as your goal. This will help you get an idea of how close you are to your target.

   Click Done to save your chart settings.

   

7. Give the chart the title New ARR and Pacer then click Save Chart to save your chart and arrange it on your dashboard.

8. Now create another chart to show how many days are left in the month. Toggle to SQL Mode and use the following SQL statement:

    select {CURRENT_MONTH.END.PART('day')} - {TODAY.PART('day')}
   

   

9. Make this chart into a Single Value chart, give the chart the title Days left, then save it to your dashboard.

   

Now you’ve got the makings of a pacing dashboard with some great potential!