Documentation Charts FAQs

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 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');
    

    Switch to SQL Mode to generate a series of datetime values

  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.

    Use a CASE statement to assign static values to each date

  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.

    Merge the Queries with an Outer Join

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

    Final Bar Line chart


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.

    Create a chart

  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');
    

    Type this into the SQL editor

  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”)

    Add an Edit Column step in the Pipeline

  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.

    Add a Case Statement into the Pipeline

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

    Merge the Datasets with an Outer Join

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

    Final Bar Line chart

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