set analysis selective dimension

[#1]

On some expressions, i would want to only consider certain dimensions and disregard the rest.

For example i have 5 dimensions (Dim1,Dim2,Dim3,Year,Month). I only want to consider ONLY Year and Month. What i've done is:

sum({$<Dim1=,Dim2=,Dim3=> Sales}

This is doable if there's only these few dimensions. But when you have a lot then it becomes unmanageable. I was thinking there must be a way in set analysis to say return everything, then on the set modifier you only pick the dimensions you want considered. Something like this but obviously does not work:

sum({1<Year=Year,Month=Month>} Sales)

Any ideas?

[#2]

Try something like this:

Sum( {-<Year=,Month=>} Sales )

It should select everything that involves year and month.

Found it in the help under Set Analysis and then the Set Operator examples (bottom one).

[#3]

Mark,

Thanks but i don't think this works. I tried it as well and it just yielded zero all the time.

Isn't that just saying from current selection, exclude year and month unselected? I just need it to only consider month and year and disregard any other selections made.

[#4]

You're right ...

A difficult problem to solve.

I will have to think about this more. Hopefully somebody else can give you a solution soon.

[johnw · Accepted solution]

I think there's an easy way that I'm not remembering, but here's a hard way that would probably work (untested) (edited to add distinct):

sum({1<Year ={'$(=concat(distinct Year ,chr(39)&','&chr(39)))'}
,Month={'$(=concat(distinct Month,chr(39)&','&chr(39)))'}>} Sales)

[#6]

Ah, so you're selecting everything where year is in all possible years and the same for month, without having to name them hardcoded ... nice.

Say Year can be 2007, 2008 and 2009, this translates to

sum({1<Year ={'2007','2008',2009'}, ....

[#7]

Thanks John, That seems to do it. Hopefully like you said there's a more simpler way for this.

[#8]

Has anyone found an easier way to do this? I'm having a similar problem, only I need to take 15 ~ 20 dimensions into consideration, which makes this solution a lot less practical.

[#9]

Yes, after looking arround for this for some time I got it:

=sum(
{1< // Disregards all selections with 1
Year=P(Year), // Using element function P() to select the Possible values for Year (this will work with Null(), 1 and many values selected in the field)
Quarter=P(Quarter), // etc...
Month=P(Month)
>}
Amount // Whatever you wanna measure
)

Hope this will help.

Diego

[johnw]

Yep, THAT'S the way to do it. Only works in 9.0 as I recall, but I suspect most people are on 9.0 by now. (We're not. )