6 Replies Latest reply on Nov 10, 2017 9:20 AM by Norbert Maijoor

# Adding a Simple Month End Forecast to a Chart

Hi,

I want to create a simple forecast to the end of the month and I want to include it as an extra bar at the end of the chart (attached).

So imagine we are on November 5th and have revenue of £10,000.  I want the calculation to be £10,000 / 5 days gone * 30 days in November.

Appreciate any help regarding putting this together.

Best,

Roddy

• ###### 1. Re: Adding a Simple Month End Forecast to a Chart

Hi Roderick,

Find my approach as reference below and stored in attached workbook version 10.3 located in the original thread.

1. Day gone & Remaining: if [date]<=today() then mid(str([date]),10,2)+"-"+mid(str([date]),6,2)+"-"+mid(str([date]),1,4) else "Remaining" END

2. Month:

3. Distribution gone: if attr([Days gone & Remaining])<>"Remaining" then  sum([value]) END

4. Distribution remaining: if attr([Days gone & Remaining])="Remaining" then WINDOW_AVG(sum([value]))*sum({fixed [date (Months)]:count([date])}) END

5. Drag the required objects to the indicated locations.

6. Right click Distribution Remaining > Text menu > tick Dual Axis and you will not be able to synchronize what not is required.

Regards,

Norbert

• ###### 2. Re: Adding a Simple Month End Forecast to a Chart

Hi Norbert,

But what if we want to use 14 days back average (no matter if it goes back in the prior month) instead of calculation based on the 1st day of the month?

• ###### 3. Re: Adding a Simple Month End Forecast to a Chart

Hi Violina,

Find my approach based on last 14 days below as reference and stored in attached workbook version 10.3 located in the original thread

Regards,

Norbert

• ###### 4. Re: Adding a Simple Month End Forecast to a Chart

Hi Norbert,

I followed all the steps you have described, but unfortunately the 'Distribution remaining' is not working for me. It is showing the exact amount as the sales per day. Do you have any idea where is the problem?

• ###### 5. Re: Adding a Simple Month End Forecast to a Chart

Hi Violina,

The issue is that you have dates only uptill 9/11 and not to the end 31/11 in your data set. A "way out" could be scaffolding the missing dates but not sure if i can combine my approach with scaffolding..

Regards,

Norbert

• ###### 6. Re: Adding a Simple Month End Forecast to a Chart

Hi Violina,

Find my approach based on scaffolding below and stored in attached workbook version 10.3 located in the original thread

Regards,

Norbert