Page 1 of 1
Cube Rule not working the same in 10.2 as 9.5.1
Posted: Fri Jun 12, 2015 1:51 pm
by RNB
['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.
Re: Cube Rule not working the same in 10.2 as 9.5.1
Posted: Fri Jun 12, 2015 4:55 pm
by tomok
Have you tried replacing the shorthand code on the right hand side with full longhand DB references?
Re: Cube Rule not working the same in 10.2 as 9.5.1
Posted: Sat Jun 13, 2015 6:33 am
by rmackenzie
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:
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 );
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.
Re: Cube Rule not working the same in 10.2 as 9.5.1
Posted: Sat Jun 13, 2015 3:34 pm
by Wim Gielis
or:
If we want 1 or 0 as the result:
[/off-topic]
Re: Cube Rule not working the same in 10.2 as 9.5.1
Posted: Sun Jun 14, 2015 8:51 am
by rmackenzie
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.
Re: Cube Rule not working the same in 10.2 as 9.5.1
Posted: Sun Jun 14, 2015 1:31 pm
by declanr
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.)
Re: Cube Rule not working the same in 10.2 as 9.5.1
Posted: Mon Jun 15, 2015 1:07 pm
by RNB
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!