Calculating a value on a level below the dimension.

[#1]

Hello,

I am creating a dashboard that projects revenue by sales team for this year based on the growth we saw last year. The complex part of what I am trying to do is calculate this projection on a level below team ( say product category) and then add up each category for each team to get the value . I do not want to use product category as a demension since there are too many and it's too much to look at. Is there a way to do this?

Any suggestions would be appreciated.

Thanks,

Jeff

[Jason_Michaelides]

You can maybe put Product Category inside a set analysis expression.  What is your current expression and what the dimensions so far?

[#3]

Hi Jason,

As of right now I only have sales team as a demension. As for expressions , I am using 3 similar expressions for revenue to calculate projected revenue.

1. Rev for this year as of today. Sum({$<RevYear={"2012"},Snapshotdate={"Today"}>}ActiveRev)

2. Rev last year (YTD) Sum({$<RevYear={"2011"},Snapshotdate={"Today-366"}>}ActiveRev)

3. Rev End Last Year Sum({$<RevYear={"2011"},Snapshotdate={"2011yrend"}>}ActiveRev)

Here is the projection expression

(3/2) *1

Is it an issue that I am already using set analysis in the expressions?

Thanks for the help!

[swuehl · Accepted solution]

Jeff,

it sounds to me that you would need to use advanced aggregation, something like

=sum(aggr( YOUREXPRESSIONS, Team, [Product Category] ))

and replace YOUREXPRESSIONS with the formula you stated above.

Hope this helps,

Stefan