Need bar chart by year (x axis) for total expenses but Version (dimension) changes by year

[#1]

So my X axis is Year and I need to plot total expenses by expense class (stacked). It seems relatively easy on the surface but for 2011 the Version to plot is Budget, for 2010 the Version to plot is Forecast, and for the years prior to 2010 the Version to plot is Actual. This will allow them to see years side by side but comparing Actual Amounts (historical) to Forecasted and Budgeted Amounts.

Thoughts?

Thx - Kevin

[boorgura]

your dimensions will be Year and Class.

and expression will be

sum(if(Year = 2011, Forecast, Actual))

P.S: You cant use more than one expression, if you have 2 dimensions.

Let me know if this helps.

[#3]

Thanks Rocky. I'm using set analysis in my expression already so it looks like I might need to combine the sum if and my set statement. Version is a dimension and Forecast, Budget, Actual are dimension members. In your recommendation, it looks like you're assuming Forecast and Actual are two separate fields but they're actual dimension members. Here's my current expression: Sum ({$<AcctType = {'Expenses'}>}Amount)

Any thoughts on wrapping in the IF condition?

Thanks,

Kevin

[boorgura]

Is it possible for you to post a sample version.

So that I can work on it?

[johnw]

Maybe this?

if(Year>year(today),sum({<Version={'Budget'} ,AcctType={'Expenses'}>} Amount)
,if(Year=year(today),sum({<Version={'Forecast'},AcctType={'Expenses'}>} Amount)
,sum({<Version={'Actual'} ,AcctType={'Expenses'}>} Amount)))

[#6]

Weird...John, your expression works if I dump just the relevant columns out and build in a test qvw but with the entire data set it's filtering every Year for Budget only. Thoughts?

[johnw · Accepted solution]

It may be a matter of how your Year field is defined. Mine is typically a date, not just a number. In that case, you could wrap your Year field in the year() function to get a consistent comparison.

if(year(Year)>year(today),sum({<Version={'Budget'} ,AcctType={'Expenses'}>} Amount)
,if(year(Year)=year(today),sum({<Version={'Forecast'},AcctType={'Expenses'}>} Amount)
,sum({<Version={'Actual'} ,AcctType={'Expenses'}>} Amount)))

[#8]

Right on John...that was it. Thanks again!