I am trying to create a calculated member in AS2005 that will allow me to
dynamically rank and/or re-calculate other calculated measures based on a
specific set of members.

Here is what I currently have:

WITH
SET OrderedCarriers AS
filter(ORDER([CARRIER].[CARRIER NAME].members,[Measures].[AVG POLICY
PREMIUM] , BaSC),not isempty([Measures].[AVG POLICY PREMIUM]))
member [Measures].[testr] as [Measures].[AVG POLICY PREMIUM]
MEMBER [Measures].[carrier Rank] AS RANK([CARRIER].[CARRIER
NAME].currentmember,orderedcarriers)


SELECT
{[Measures].[carrier Rank],
[testr]} ON 0
,
nonempty([CARRIER].[CARRIER NAME].members )

ON 1
from [Carrier-Hanover]



The carrier member is what will be dynamic. The user can select a multitude
of members (on rows) and based on what members are selected, re-calculate the
rankings.

I am sure its possible, I just need a little help in getting there

Thanks.

Hi,

The key is to define a dynamic set for the carrier member. I have not
personally done this, but exisiting statement may do the trick. use the
syntax:

exisitng [CARRIER].[CARRIER NAME].members

this will give you all the members in the query context.

"jgb23" wrote:

So using the existing keyword, I am trying to put it into the context of a
Calculated measure

here is what I have so far:


RANK([CARRIER].[CARRIER NAME].currentmember,
(filter(
ORDER(
{existing([CARRIER].[CARRIER NAME])},
[Measures].[AVG POLICY PREMIUM] ,BaSC
)
,not isempty(
[Measures].[AVG POLICY PREMIUM]
)
)
)
)

I do have the calculation working for the whole set (all carriers). When I
use the Existing keyword as above, the rank turns to 1 for all members. What
am I doing wrong?

I guess the exising set only return the currentmember in the rank() function,
thus ranking against itself always return 1. You may want to try define a
set first:

with set [mySet] as '{existing([CARRIER].[CARRIER NAME])} ' first,
then substitute this in the your rank expression.

Good luck.

"jgb23" wrote:

Yongli,

to keep this in the context of a Calculated measure, I dont believe I can
define a set in the calc measure. This is my current version of the CM. Its
now dynamically calculating. With my test data, I have 4 carriers with data.
When it ranks them, it ranks 2 carriers as 1 and 2 carriers as 2. The
measure it uses for the rank is not close by any carrier. When I chose both
of the carriers that are either 2 or 1, it re-ranks them correctly. Here is
the code:

IIF(isleaf([CARRIER].[CARRIER NAME]),
RANK([CARRIER].currentmember,
(filter(
ORDER(
existing([CARRIER].members),
[Measures].[AVG POLICY PREMIUM] ,BaSC
)
,not isempty(
[Measures].[AVG POLICY PREMIUM]
)
)
)
),null)

I just dont get why it ranks 2 of them as 1 and the other 2 as 2.

"yongli" wrote:

Hi,

On second thought, the existing statement will not work in the rank()
function, since the context will be always your currentmember, there is no
way it can predict all your selected member set in advance. By the way, do
you have a MDX which will use your CM? based on the MDX, maybe we could
construct the CM differently.

"jgb23" wrote:

Yes, the CM MDX is in my last post.

"yongli" wrote:

Let's wait to see if anyone else has some cracks at this. I would like to
know how it can be resolved.

"jgb23" wrote:

If you always select your members on rows, you can use Axis(1) to retrieve
the selected member.

so use Axis(1) in your order statement, that will get what you want.

"jgb23" wrote:

what about using the axis() function? Couldnt I leverage that?

"yongli" wrote: