Consolidating values based on picklist

[Rebekah]

Hi,

I have 2 cubes : 'SCOA Capture V3' and 'SCOA V3 Elimination No'

Dimensions of 'SCOA Capture V3', are REBusinessUnit, SCOA V3 Account Hierarchy, SCOA Segments Capture V3
Dimensions of 'SCOA V3 Elimination No', are REBusinessUnit, SCOA Segments Capture V3, SCOA V3 Elimination No, SCOA V3 Elimination Measure

'SCOA V3 Elimination Measure' has a an element called 'Target Key', which is a picklist that maps to 'SCOA V3 Account Hierarchy'
' SCOA V3 Elimination No' is a 2 level hierarchy, level 1 has 'Total', level2 has a number range from 1 to 2

My Rule below :

Code: Select all

[] = N: IF(!SCOA V3 Account Hierarchy @= DB('SCOA Eliminations V3',!REBusinessUnit,!SCOA Segments Capture V3,'1','Target Key'), DB('SCOA Eliminations V3',!REBusinessUnit,!SCOA Segments Capture V3,'Total','Target Value'),continue);
[] = N: IF(!SCOA V3 Account Hierarchy @= DB('SCOA Eliminations V3',!REBusinessUnit,!SCOA Segments Capture V3,'2','Target Key'), DB('SCOA Eliminations V3',!REBusinessUnit,!SCOA Segments Capture V3,'Total','Target Value'),continue);

If the same account it chosen for 2 different elimination no's, rule works perfect. (Gives the consolidated total)
But when different accounts are chosen for 2 different elimination no's, rules doesnt work. (Gives the consolidated total for each account).

I need it to give me the consolidated item per picklist values.

Please Help ...

[tomok]

Not exactly sure what you are expecting. Since the cube has the dimension SCOA V3 Elimination No and that dimension has two elements, 1 and 2, then that is two separate intersections in which to store your account lookup value (what you are calling a "picklst"). Each lookup is going to be done separately, based on the value stored in Target Key. If you input different accounts in Target Key for the the intersections Elim #1 and Elim #2 then you'll get different values. I don't have any suggestions as I don't understand the business purpose of what you are trying to accomplish.

[asj105]

It is sound like you need to accumulate values based on picklist, Please find following post. This might answer your query. http://www.tm1forum.com/viewtopic.php?f=3&t=3985