Actual to Forecast Line Chart Format

ChrisAHeathcote
ChrisAHeathcote
in User Experience

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?

Tagged:

Best Answer

  • ChrisAHeathcote
    ChrisAHeathcote
    Answer ✓

    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.

    ChrisHeathcote_1-1607680700872.png

    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

    ChrisHeathcote_2-1607680814817.png

    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.

    ChrisHeathcote_3-1607680947102.png

    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 -

    ChrisHeathcote_4-1607681193618.png

     

     

     

Categories