Quick Way to Create Static Forecast Values - Bar/Line Chart


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. Below are steps to create a bar line chart with static forecast values.

For this example I want to display total users over time versus a company’s projected user total for the current year.

To start, create your first layer using the actual user count on a monthly basis, which I named “User Count”.

Next we will need to create a second layer with our static forecast values, which we will name Projections. See static forecast values below:

Within SQL mode add the following statement to create a new time series column beginning January 2016 through December 2016:

The next two pipeline steps within the Projections layer will be prior to the merge and will not apply to the User Count layer.

Now that we have our new “generate_series” column above, we will add an “Edit Column” step and insert a custom strftime function:

Next create a case statement that only applies to the Projections layer assigning each month of the year our corresponding static forecast amount.

Lastly, merge the two layers with an Outer Join.