Testing for a text value returning a number

[fourfive]

Still a newbie

I have a dimension of Versions that contains numeric items 'Rolling Forecast', 'Forecast', 'Actual' and a string item 'Act/For Flag' that contains either the word 'Forecast' or 'Actual'

My rule is testing for the 'Forecast' or 'Actual' and depending on the result makes 'Rolling Forecast' either 'Forecast' or 'Actual'

This is my rule

['Rolling Forecast']=N:IF(['Act/For Flag']@='Forecast',['Forecast'],['Actual']);

When saving I get this error message
Syntax error on or before:['Act/For Flag']@='F
logical expression

[tomok]

When testing for strings you have to use the full DB(CubeName,Dim1,Dim2,....Dimx) notation on the right-hand side, not the shorthand [] type:

Code: Select all

['Rolling Forecast']=N:IF(DB(CubeName,!Dim1,...!Dimx,'Act/For Flag')@='Forecast',['Forecast'],['Actual']);

BTW, this is a pretty bad design. Why would you need to store the string value 'Forecast' in every intersection in the cube??? Better to just have an attribute on the Version dimension, store it once, and use that attribute value in the rule:

Code: Select all

['Rolling Forecast']=N:IF(ATTRS('Version',!Version,'Act/For Flag')@='Forecast',['Forecast'],['Actual']);

[fourfive]

Thanks Tomok,

I'm interested in your second solution but not clear if it's appropriate.

Here's what I'm trying to do
My cube is a Profit and Loss cube which is dimensioned by P&L, Month and Versions (Versions with the rule you fixed).

I have another two dimensional cube called Calendar where the administrator inputs the word 'Actual' or 'Forecast' against the Months. So if the business has just closed its March accounts it looks like this

Jan Actual
Feb Actual
Mar Actual
Apr Forecast
May Forecast
....
Dec Forecast

For April of course the administrator will advance the calendar by inputting Actual against Apr.

I have a rule in my P&L cube that takes the value Actual or Forecast from the Calendar, and its this same value that the original rule refers to. Seemed a cool way of doing it but I can see its spraying the cube with lots of 'Forecast' and 'Actual'

[tomok]

I have a rule in my P&L cube that takes the value Actual or Forecast from the Calendar, and its this same value that the original rule refers to. Seemed a cool way of doing it but I can see its spraying the cube with lots of 'Forecast' and 'Actual'

Be careful with rules in TM1. Things that seem "cool" often result in poor design and/or poor performance. Better to stick with KISS unless there is a valid business reason to be cool. In your case you have everything you need to make it KISS as you already have a cube with which version the Rolling Forecast should be populated with in the Calendar cube, no need for any IF statement. Just modify your rule to:

Code: Select all

['Rolling Forecast']=N:DB(CubeName,!Dim1,!Dim2...!Dimx,DB('Calendar',!Month,!Versions));

This is not the EXACT correct syntax because I don't know the dimensionality in your cube(s) but it should give you the gist of it.