Tm1 Rules with Date and month

Post Reply
manu0521
Posts: 124
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: IBM TM1, Planning Analytics
Version: PA 2.0.5
Excel Version: 2016

Tm1 Rules with Date and month

Post by manu0521 »

Hi Guys,

I wanted to know how to perform the below task .

I have a cube with months dimension like
Year - 2018
Month - 201801
Date - 2018-01-01

Now this is calendar year and values are stored in day level.
Now there is a rule in the cube which goes to exchange rate cube which has a different month dimension at month level .

Fiscal year
201801 is 201807 in the exchange rate cube .

Now in my rule for the days I have to do a n level rule and get the correponding fiscal month value from exchange rate cube.

[ Currency:'EUR', Lease Measures:'Right of Use Asset' ] = N: ([ Currency:'Local Currency', Lease Measures:'Right of Use Asset' ] * (DB( 'Exchange Rates', 'Actual', ATTRS( 'Companies', !Companies, 'Functional Currency' ), 'EUR', 'Ending Rate', 'convert 2018-01-01 to 201807' ));

I am thinking of some substring function, but then I have to get the fiscal year equivalent.

If anyone can suggest some logic in the rule , it would be of real help.

Thanks,
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Tm1 Rules with Date and month

Post by Wim Gielis »

You could use an attribute at the day level and retrieve that value ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
manu0521
Posts: 124
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: IBM TM1, Planning Analytics
Version: PA 2.0.5
Excel Version: 2016

Re: Tm1 Rules with Date and month

Post by manu0521 »

so you are asking me store an attribute at day level .

so for 2018-01-01 i store an attribute value of 201807 right?

That should work ,let me try that.

Thanks,
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Tm1 Rules with Date and month

Post by Wim Gielis »

Exactly.
Or, you rework the day element to a month name, which itself has an attribute to the related month name. That can be an option too.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply