Set analisys - hot to get a value from a another menber off dimension

[#1]

Hey,

I have a expression that needs an aggregated values that is in another member of the same dimension.

Something like to compare sum(sales) of vendor A with B, to calculate a percentage.

I have tried set analysis like the follow and didn't worked

Sum( {1<Vendor = {"B"}>} sales)

Thanks!

[sunny_talwar]

The expression might be showing error, but did you try clicking okay to check if you see any result?

[#3]

No, i didn't see.

[swuehl · Accepted solution]

The expression with TOTAL qualifier should work.

If Mes is a field in your data model, try

=Sum({$<vendor={'a'}>}  TOTAL<Mes> value)

in your chart as shown above.

If this works, you need to make the expression also work for changing the dimension group.

You can either add all your dimension fields of the dimension group to the field list, assuming another dimension group field Ano:

=Sum({$<vendor={'a'}>}  TOTAL<Ano, Mes> value)

or retrieve the currently used dimension field using GetCurrentField(), just like Sunny suggested. You need to replace [Group Name] with the name of the dimension group, of course.

Test the dollar sign expansion using

=Sum({$<vendor={'a'}>}  TOTAL<$(=GetCurrentField([Group Name]))> value)

in your chart, but leaving the expression label empty (and again, make sure to use the correct dimension group name).

Now the chart expression header should show the expanded expression. Does the TOTAL qualifier shows the currently used dimension field in its list?

[#5]

The first one works, the other one didn't work.

I will do another test, with my real data.

[swuehl]

Could you describe your drill down group a bit more detailed?

What's the name of the group? Which fields are in there?

Which expression are you finally using?

And with which dimensions drilled into does the expression work in with which doesn't?

[Saravanan_Desingh]

Hi Sunny & Stefan,

Can you please tell me if the both Set Analysis are equivalent?

= Sum(TOTAL <$(=GetCurrentField([Group Name]))> {$<vendor={'a'}>} value)

=Sum({$<vendor={'a'}>}  TOTAL<$(=GetCurrentField([Group Name]))> value)

If so, is the set expression and modifier interchangeable? Sorry, about this basic question. I normally use the second type in my Apps.

[sunny_talwar]

= Sum(TOTAL <$(=GetCurrentField([Group Name]))> {$<vendor={'a'}>}value)

The portion is red is not set analysis, it is TOTAL qualifier with the field name in the form of dollar sign expansion.

The portion in pink is set analysis

AFAIK, TOTAL and set analysis are interchangeable.

[Saravanan_Desingh]

Yes. I realized now. Thanks Sunny ..