Average of Two Values

[Naven]

Hi,

I have 2 cubes in my TM1 application, "Trade Cube" and "Rate Cube"

In the Rate Cube rates are published for different standard days.

The matrix below displays high level Rate Cube Structure
Days     Rates
15         0.25
30         0.3
60         0.2
90         0.4
120       0.1

In the Trade Cube we need to calculate an amount using these rates (trade Amount*Rates).

Trd ID     Days     Trade Amount     Rate
1234        82        1000                   

In the trade cube the day value is 82, since the exact value is not present in Rates cube we need to take average of two values falling on either side of 82 i.e. 60 & 90 in this case.

Could you pls help me with design approach to be followed to fulfill this requirement.

Attached is the screenshot of cubes for better understanding.

Thanks

[ravi_prasad_d]

HI,
There are different ways to do this.

(1) using Rules
(2) using TI

[ravi_prasad_d]

I gave a screenshot ....how to do using rules in the attachment.

[lav4you]

Hi Naven,

You can write a rule condition

[Ask Rate 1] = IF (Days > 30 AND Days < 60, DB (Rate Cube,  Ask Rate, 30)., Continue);
[Ask Rate 1] = IF (Days > 60 AND Days < 90, DB (Rate Cube,  Ask Rate, 60), Continue);....
And rest of the conditions

[Ask Rate 2] = IF (Days > 30 AND Days < 60, DB (Rate Cube,  Ask Rate, 60)., Continue);
[Ask Rate 2] = IF (Days > 60 AND Days <90, DB (Rate Cube,  Ask Rate, 90), Continue);....
And rest of the conditions

this rule is not accurate but to give an idea in general.


Note. You can directly calculate average without storing upper and lower rate.

Regards,
Jitesh