Selecting values ​​from the interval using IF

[Antrac1t]

Hello,
I have some problems with select value from the interval through two cubes.
1, cube (Discounts) contains these dimensions: Intervals and Intervals metric (in context are Suppliers and Materials)

2, cube (Amount selection) contains these dimensions: Materials and Materials metric (in context are only Suppliers)


Now i want that when i will write some amount one material. Then I want obtain the price from a given interval, where the quantity is. This is a discount system.
For this problem i used this IF:
['price']=IF((DB('Discounts',!Intervals,!Suppliers,!Materials,'from')<['amount'])&(DB('Discounts',!Intervals,!Suppliers,!Materials,'to')<['amount'])<['amount']),DB('Discounts',!Intervals,!Suppliers,!Materials,'Price'),0);
(this rule is written in a cube "Amount selection")

Unfortunately it does not work but I think it should: D.

All this is done in the Performance modeler and Architect (10.1.1)

Thx for help guys.

[paulsimon]

Hi

The problem is that your rule in Amount Selection is referring to dimension !Interval, but that does not exist in this cube. Your rule should also be written at the N: level.

If you only ever have 4 intervals then the following will work. If the number of intervals is variable then a more complex technique using recursion or using TI will be needed.

Code: Select all

['price']=N: 
  IF( ['amount'] < DB('Discounts','1',!Suppliers,!Materials,'To')
       ,
       DB('Discounts','1',!Suppliers,!Materials,'Price')
       ,
       IF( ['amount'] < DB('Discounts','2',!Suppliers,!Materials,'To')
            ,
            DB('Discounts','2',!Suppliers,!Materials,'Price')
            ,
            IF( ['amount'] < DB('Discounts','3',!Suppliers,!Materials,'To')
                 ,
                 DB('Discounts','3',!Suppliers,!Materials,'Price')
                 ,
                 DB('Discounts','4',!Suppliers,!Materials,'Price')
            )
       )
  ) ;

This rule is written using the Rule Editor rather than performance modeller.

&(DB('Discounts',!Intervals,!Suppliers,!Materials,'to')<['amount'])<['amount']),DB('Discounts',!Intervals,!Suppliers,!Materials,'Price'),0);
(this rule is written in a cube "Amount selection")

Hello,
I have some problems with select value from the interval through two cubes.
1, cube (Discounts) contains these dimensions: Intervals and Intervals metric (in context are Suppliers and Materials)

2, cube (Amount selection) contains these dimensions: Materials and Materials metric (in context are only Suppliers)


Now i want that when i will write some amount one material. Then I want obtain the price from a given interval, where the quantity is. This is a discount system.
For this problem i used this IF:
['price']=IF((DB('Discounts',!Intervals,!Suppliers,!Materials,'from')<['amount'])&(DB('Discounts',!Intervals,!Suppliers,!Materials,'to')<['amount'])<['amount']),DB('Discounts',!Intervals,!Suppliers,!Materials,'Price'),0);
(this rule is written in a cube "Amount selection")

Unfortunately it does not work but I think it should: D.

All this is done in the Performance modeler and Architect (10.1.1)

Thx for help guys.

[Antrac1t]

Thx for reply paulsimon, but its how you said ... i have intervals 1-n and in TM1 can not be used while ...
+ in amount selection i can write 1-n amounts
flour 150
water 50
salt 0