Create static forecast values

Many times, projections are calculated as X% growth over the previous month. However, there are instances where projections calculated on your end are not true linear projections. In those cases, you may want to insert static values within your Bar Line chart when comparing your company’s performance to the company’s forecast.

For this example, we want to display total users over time versus a company’s projected user total for the current year. We’ll use the Dundersign Demo Data data source to do this.

• In Visual SQL
• In Data Explorer

In the Visual SQL interface

1. The first query will contain the actual number of users over time.
   1. For your first Query, add the Created Date and Id column from the Users table to the Columns section.
   2. Change the time bucket for Created Date to Month.
   3. For clarity, rename the User Id column to User Count.
   4. Add Created Date to the Filters section, choose between and including for the filter operator, and use 2019-09-01 and 2020-10-01 as the start and end dates, respectively.
   5. Click Run Query.

2. Add another Query—this will contain the projected number of users for the current year. Switch to SQL Mode and use the following query to create a new time series column that starts in September 2019 and goes through October 2020:

    select * from generate_series('2019-09-01 00:00'::date, '2020-10-01 12:00'::date, '1 month');
   

   

3. Staying on Query 2, use an Apply Formula Action to edit the “generate_series” column. Select Custom as the formula type and use the following SQLite formula to format the datetimes:

    strftime('%Y-%m',"generate_series")
   

4. Still working on Query 2—Add a Formula Column, select Custom for the formula type again, and use the following CASE statement:

    case
        when "generate_series"='2019-09' then 1000
        when "generate_series"='2019-10' then 1400
        when "generate_series"='2019-11' then 1200
        when "generate_series"='2019-12' then 1250
        when "generate_series"='2020-01' then 1400
        when "generate_series"='2020-02' then 1300
        when "generate_series"='2020-03' then 1550
        when "generate_series"='2020-04' then 1500
        when "generate_series"='2020-05' then 3100
        when "generate_series"='2020-06' then 3750
        when "generate_series"='2020-07' then 5000
        when "generate_series"='2020-08' then 6510
        when "generate_series"='2020-09' then 7900
        when "generate_series"='2020-10' then 8125
        else 0
    end
   

   This CASE statement assigns a corresponding static forecast amount to each month of the year. Use whatever static values you’d like for this example!

   Make sure the years and months in this step correlate with the dates of your data in Query 1 and Query 2.

   

5. Rename the Formula Column to Projected User Count for clarity.

6. Click the Join Action to see the final merge results. Leave the join type as Outer Join.

   

7. Change your chart to a Bar Line chart and adjust the chart settings as needed.

   

In the Data Explorer interface

1. Create your first Dataset by dragging the User Id column into the Measures field and Created Date column into the Dimensions field. Change the Time Bucket of the Created Date column to Month. Change the User Id column’s label something notable, like User Count.

   

2. Scroll down to the Data Pipeline and click the plus (+) next to Dataset 1 to create a new Dataset and query. For this new Dataset (Dataset 2), switch to SQL Mode and type the following to create a new time series column beginning in September 2019 through October 2020.

    select * from generate_series('2019-09-01 00:00'::date, '2020-10-01 12:00'::date, '1 month');
   

   

3. In the Pipeline, click the plus button underneath Dataset 2 to add a Pipeline Step and select Edit Column.

   You’ll want to edit your newly created column named “generate_series” and select the Custom formula option. Enter the following SQLite formula into your Formula field and click Apply & Close.

   strftime(‘%Y-%m’,”generate_series”)

   

4. Still working with Dataset 2, add another Pipeline Step—this time selecting the Case Statement step.

   You’ll now create a Case Statement to assign each month of the year a corresponding static forecast amount. Attach a forecast value (in this example, they are made-up values) for every month of the year.

   Make sure that the years and months in this step correlate with the dates of your data in Dataset 1 and in SQL for Dataset 2. Click Apply & Close when completed.

   

5. Click the Merge Datasets step in the Pipeline, select Outer Join from the Merge Type dropdown menu and click Apply & Close.

   

6. Switch your chart to a Bar Line chart and adjust the chart settings as needed.

   

That’s it! You now have a chart displaying your actual values as bars and your forecasted values as a line.