['Open Forecast', {'USD Translation Rate', 'USD Actual Rate'}]=N:if(!FinanceGLPeriod@<=DB('Finance Constants','Constant Current Fiscal Period','StringValue'),['Actual','USD Actual Rate'],continue);
['Open Forecast']=N:if(!FinanceGLPeriod@<=DB('Finance Constants','Constant Current Fiscal Period','StringValue') ,['Actual'],continue);
Once the FinanceGLPeriod is <= the 'Constant Current Fiscal Period' most of the data for the prior months is set to zero for the 'Open Forecast' Even though the data exists in the 'Actual' version. This code works in the 9.5.1 version but not the 10.2 version. Can anyone see why this is producing disparate results between the two versions?
Thanks,
RNB.
Cube Rule not working the same in 10.2 as 9.5.1
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Cube Rule not working the same in 10.2 as 9.5.1
Have you tried replacing the shorthand code on the right hand side with full longhand DB references?
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Cube Rule not working the same in 10.2 as 9.5.1
Your code is flawed because you are testing using @<= instead of <=. Testing numeric inequality on strings exposes you to a lot of problems to do with sort orders/ character encoding etc. I'd suggest you re-code this logic like so:
Note I've assumed you need a numeric attribute on FinanceGLPeriod and a numeric measure in the measure dimension of your Finance Constants cube.
Try this in Excel if you're not sure what I am talking about:
Re your TM1 version - the TM1 engine developers do make undocumented tweaks to tighten up the rule engine and presumably they've done something to impact this code given they need to think about a number of string encoding implementations e.g. ASCII and UTF-8 and what-have-you.
Code: Select all
['Open Forecast', {'USD Translation Rate', 'USD Actual Rate'}] = N:
if( ATTRN ( 'FinanceGLPeriod', !FinanceGLPeriod, 'Period Number' ) <= DB('Finance Constants','Constant Current Fiscal Period','Numeric Value'),
['Actual','USD Actual Rate'],
continue );
Try this in Excel if you're not sure what I am talking about:
Code: Select all
=IF("3"<="11",TRUE,FALSE)
Robin Mackenzie
-
- MVP
- Posts: 3120
- 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: Cube Rule not working the same in 10.2 as 9.5.1
or:rmackenzie wrote:Code: Select all
=IF("3"<="11",TRUE,FALSE)
Code: Select all
="3"<="11"
Code: Select all
=--("3"<="11")
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
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
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Cube Rule not working the same in 10.2 as 9.5.1
I guess that the OP has their financial periods coded as 01, 02, 03 ... 11, 12 so the comparison has always worked, e.g.
resolves to TRUE, even though:does not.
I assume something like this regularly does the rounds at IBM.
Code: Select all
="03"<="11"
Code: Select all
="3"<="11"
I assume something like this regularly does the rounds at IBM.
Robin Mackenzie
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Cube Rule not working the same in 10.2 as 9.5.1
What exact version of 10.2 are you on?
The base version saw a few issues with how rules were handled (and the first couple of fix packs even.)
The base version saw a few issues with how rules were handled (and the first couple of fix packs even.)
Declan Rodger
-
- Posts: 5
- Joined: Mon Jan 05, 2015 9:47 pm
- OLAP Product: Cognos
- Version: 9.5.2
- Excel Version: 2010
Re: Cube Rule not working the same in 10.2 as 9.5.1
Thank you so much. This is so helpful I appreciate the help very much.
I'm on 10.2.2 and it seems fully qualifying the right side of the equation with the dimension name [Dimension:'Actual',Dimension:'USD Actual Rate'],continue);
corrected the issue. Now I will need to start looking at the code itself to try the improvements suggested.
Thanks again for the help!
I'm on 10.2.2 and it seems fully qualifying the right side of the equation with the dimension name [Dimension:'Actual',Dimension:'USD Actual Rate'],continue);
corrected the issue. Now I will need to start looking at the code itself to try the improvements suggested.
Thanks again for the help!