Look up cube with ranges

[appleglaze28]

I'd like to ask since I feel like TM1's IF Function is limited and in the Rules...what's the best way to approach if you have like a 10 sets of range to determine the value.

For example:
If the Average Salary > 1000 THEN 29
If the Average Salary > 1500 THEN 30
If the Average Salary > 2000 THEN 50

And so on and so forth...Since the rules doesn't have Else how do I approach this kinda of lookup? I'm still thining of how its going to be done if I create a cube for it.

[David Usherwood]

TM1 rules do else, they just do it excel-style:
['x'] = n:
if(<something>,
<true output>,
<false output>
);

Well brought up TM1 boys and girls put the else in as a comment ie

['x'] = n:
if(<something>,
<true output>,
#else
<false output>
);

Continue can be useful here to break up lots of nested ifs. I also like to open up the calculations into a 'column' dimension and cross add the results - easier to read.

[jstrygner]

I do not know what David ment by "open up the calculations into a 'column' dimension and cross add the results", but I assume something else than my idea below

Assuming you have regualr range intervals (in your example it is 500) you could think of such a combination:

1. You build a 'Ranges' dimension with element names like 1000, 1500, 2000 and add a 'Result' attribute for this dimension that stores values you want to be assigned (accordingly: 29, 30, 50)

2. You write a rule that looks more or less like this one:
['X']=N:
ATTRN('Ranges', STR(INT(['Average Salary']/500)*500, 20, 0), 'Result');

So for example if Average Salary equals 1611.26 then your rule will finally check the ATTRN('Ranges', '1500', 'Result') and return 30.

Of course you need to modify this a little bit more, because my example will return 30 also if Average Salary equals exactly 1500 and in your example it is supposed to return 29, I just wanted to share my general idea. HTH