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 will be using the PostgreSQL Chartio Demo Source to do this.
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 July 2018 through June 2019.
select * from generate_series('2018-07-01 00:00'::date, '2019-06-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.
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.