Tm1 Rules with Date and month

Post Reply
manu0521
Posts: 43
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2010

Tm1 Rules with Date and month

Post by manu0521 » Sun Jun 02, 2019 12:23 am

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: 2052
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Tm1 Rules with Date and month

Post by Wim Gielis » Sun Jun 02, 2019 3:45 am

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

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

manu0521
Posts: 43
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2010

Re: Tm1 Rules with Date and month

Post by manu0521 » Sun Jun 02, 2019 12:26 pm

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: 2052
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Tm1 Rules with Date and month

Post by Wim Gielis » Sun Jun 02, 2019 1:04 pm

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

Post Reply