Actual to Forecast Line Chart Format

ChrisAHeathcote

Hi Guys, 

Looking to create a simple line chart where I can view actuals in one colour followed by other versions in a separate colour.

I would like to see actuals transition into forecast / budget starting from where actuals end and continuing into future time periods.

This should be a simple formatting issue but I am stuck!!

Any thoughts?

ChrisAHeathcote

I was able to solve this particular challenge. Here is how I did it....

First task is to build a filter module where I  run through the various IF THEN ELSE statements to create a grid which would allow me to determine which version over which time period I pull through to my working chart module.

There a three possible combinations of version and time that need to be taken into account;

1. Actual version for actual time
2. Non Actual versions for future time
3. Non Actual versions for current period

These formulas produce the grid which I can use in all future chart modules to determine which values need to be calculated for which version and time period.

I can now use this grid to inform my IF THEN ELSE statement in the chart module. In this example I am using a line item subset from the target module and collect() to pull that data in and the filter module to determine which combination of version and time are valid.

The Divide(0,0) closing out the query is used to eliminate all zeros which when left in the data causes the zero data points to be visible for forecast and budget versions. 

The final outcome -