Set Analysis Expression Help

[#1]

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

[#2 · Accepted solution]

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.

[#3]

Hi Marc,

Thanks for your response.

Your expression worked, however, I'm having issues with the bar colors.

I did the following. I went to Chart Properties->Colors Tab-> under Data Appearance and clicked on the color to change. in the dialog that opened under Base Color, I clicked "Calculated" followed by your expression for color change. But it did not work. I extended the syntax by making it

if(Month>Month(Today()),RGB(255,255,242), RGB(0,0,255)) with no luck.

I cannot find a bar background though I see Frame Background.

I looked at the sample file, but I do not see a calculated condition or a bar background setting.

What am I missing?

[#4]

Go to the expression dialog, and click the + next to the expression for actual/forecast. This is where the bar background color is is.

[#5]

Got it

Thanks