Calculate Rank Order based on weighting

[BigG]

Hi, I am aiming to use Contributor component of TM1 so would like to do this in rules, but may have to calculate through a TI if not...

I have a cube that calculates a weighting for leaf node project elements within a portfolio dimension, this weighting I want to use to rank each item from number 1, 2 , 3, etc to the end of the leaf node of the dim. I cannot think of a way to do this in rules (cant run the TI process from Contributor).

Once the ranking order is defined I will use this to spread total funding over the cost of each project (starting at 1) until the funds run out. This would be preferably in cube rules as well.

Any suggestions would be greatly appreciated

Cheers

[lotsaram]

You could create a dynamic subset using ORDER function in MDX to order members by the ranking value in the cube. (Even if not creating the dynamic subset on the fly will still require some TI to then process the subset.)

It might be possible to do it all with rules if you have an additional auxiliary cube with a "funding rank" dimension and "funding progress" hierarchies in that dimension. In fact I'm pretty sure you could get this to work. Just need to be careful to stay clear of circular references.

[BigG]

Hi, Went down the path of TI process...using 9.5.2 seems to have an issue with Dynamic Subsets in a view for a Ti process Data Soource... any ordering is ignored through ORDER MDX... although when you look at the view of the cube in cube viewer it is ok (and ok in subset editor)... just some random order runs through the TI process

I use an attribute for the MDx ORDERing ... which in theory should work... Does anyone have aworkarund for this or theory?

{
ORDER(
{TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[Dim].[Summarylevel]},ALL,RECURSIVE)}, 0)}
, [Dim].[attribute], BDESC)
}

[rozef]

Hi,

I already saw the same behaviour with ORDER function, I had several dynamic subsets pointing on the same cube values. So you should check if you have no other dynamic subsets using this attribute, it's maybe the reason but I will be not surprise if this MDX function have bugs.
In my case I have make a sub process that use a view on ranked perimeter.

By the way, you should be able to make the second part works by rules not too hardly with 2 more indicators or attributes. One calculating a inversed ranking or a ratio of the rank, the second will spread your cost depending on the first.
For a calulated ranking, I have no other idea then a huge rule checking all leaf nodes.

Cheers,

[BigG]

I ended up doing following steps:
1) TI process - creates subset through MDX using parameters (no datasource) - rank was ASC
2) TI process - create a static subset with MDX dynamic subset as datasource - used the order of the dynamic subset to ensure DESC order in static
3) TI process - Attached static subset to a datasource view - ran through ranking order to count 1, 2,3 etc - cellputn these values back in the cube
3) TI process - Funding $ cube as data source and spread over each ranked element until money ran out (by looping through static subset - assource does not have the prioritised list in it)...

Works ...