4 Replies Latest reply: Oct 3, 2011 4:05 PM by Amitesh Modi RSS

    Set Analysis Expression Help

      Hi All,

      I'm trying to create a chart to show 1. Budget Revenue (for entire Year Jan-Dec), Actual Revenue ( From Jan to Current month) and Forecast Revenue (From Next Month to Dec). There is only one measure used for these which is "Amount".

      The Dimension used is Month.

      The expressions that I have so far (I know not much) are:

      Budget: Sum ({<VersionID={'2011v6'}>} Amount)

      Actuals: Sum ({<VersionStatus={current}>} Amount)

      Forecast: Sum ({<VersionID={'2011v7'}>} Amount)

       

      My Version ID field looks like 2011v1, 2011v2 and so on.

      there is also a version Key field with integer values 20, 21,22 and so on that I can manipulate. I do not know yet how the business would like the versions for Budget and Forecast handled so I have them hardcoded.

       

      As is probably evident from description above the Forecast numbers show up in the chart from the month after the Actuals end.

      So, my question is how can I dynamically show data for Budget from Jan to current month and forecast data starting next month to December.

      I'm trying to include something like $(=Month(today()) ), (=Month(today())-1) and so on to get the various months.

       

      Also  I think I cannot use Max(Month #) as we have data for a number of months in future for every version (which is Forecast Data)

       

      If anyone could help with the correct expression I would be very grateful.

       

      Thanks,

      Amitesh

        • Re: Set Analysis Expression Help

          You could create a combo chart.

           

          Expression 1 Budget (Symbol): Sum ({<VersionID={'2011v6'}>} Amount)

           

          Expression 2 Actual/Forecast (Bar): if(Month>Month(Today()),Sum ({<VersionID={'2011v7'}>} Amount),Sum ({<VersionStatus={current}>} Amount))

           

          Change The bar backround color: if(Month>Month(Today()),RGB(255,255,242))

           

          See sample file Financial Controlling P&L Trends tab for a better example.