COGNOiSe.com - The IBM Cognos Community

Planning & Consolidation => TM1 => Topic started by: Naven on 17 Apr 2012 01:32:35 PM

Title: Average of Two Values
Post by: Naven on 17 Apr 2012 01:32:35 PM
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
Title: Re: Average of Two Values
Post by: ravi_prasad_d on 21 Apr 2012 06:51:05 AM
HI,
There are different ways to do this.

(1) using Rules
(2) using TI
Title: Re: Average of Two Values
Post by: ravi_prasad_d on 21 Apr 2012 06:52:48 AM
I gave a screenshot ....how to do using rules in the attachment.
Title: Re: Average of Two Values
Post by: lav4you on 18 Jul 2012 09:41:14 AM
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