Exploring TM1 - a Chartertech Company
Search
Close this search box.

String Rules Invalid Expression

A colleague of mine was creating a rule recently that did a simple Stet or Continue based on a calculated string. She was, however, getting an error saying there was an invalid expression whilst trying to save the rules. Luckily the solution was pretty easy, so we thought we’d share it with you!

Situation

We had a cube where there was an element that indicates if a set of days are to be included in a forecast inventory calculation or not. We could have combined all of this into a complex rule, however, we wanted the user to see easily if a day was forecast or not. Therefore in our measures, we have an element called ‘Plan Active?’ and then a rule that calculates a string output of ‘Yes’ or ‘No’ depending on a set of parameters.

We are then using the output of that rule to do further calculations. It was in these further calculations that we were getting the “invalid expression” rule error.

Problem

The issue arose because we were testing if a cell equalled ‘Yes’ or not. She had used square brackets to refer to ‘Plan Active?’ and was then getting rule errors when saving. Grrr!, we thought.

Solution

Fixing it was then rather easy when she worked out how.  All she needed to do was use a full DB reference rather than just a square bracket reference to the element she wanted and bingo, it worked!

So we ended up with a rule that looked like this. It is the use of the DB in the second block that resolved the error, in place fo a [] style reference:

# Setup Active Days for Planning using  'Days for Production Planning' from the Assumptions cube

['Plan','Plan Active ?'] = S: 

IF ( DayNo (  !Year | '-'  | ATTRS ( 'Month',!Month,'MonthNum') | '-' |  !Day )  <= DayNo ( Today(1) ) + DB ( 'Assumptions', 'No Year', 'No Month', 'No Day', 'Plan', 'Days for Production Planning' ) &

DayNo (  !Year | '-'  | ATTRS ( 'Month',!Month,'MonthNum') | '-' |  !Day )  >= DayNo ( Today(1)  ) 

, 'Yes' 

, 'No' ) ;

# Stet all periods in the Plan scenario that are not 'Active'

['Plan'] = N: 

IF ( DB('Inventory',!Year,!Month,!Day,'Plan',!Product,'Plan Active?') @= 'Yes'

% ( !Year @= 'No Year' & !Month @= 'No Month' & !Day @= 'No Day' )

, Continue 

, Stet );

You can see the use of the DB function in the last IF rule directly above.

So there we have it, the solution to an invalid expression when trying to create a rule calculating from a string element!

  • This field is for validation purposes and should be left unchanged.

Post Sections

Related Posts

John Vaughan

John Vaughan

John is a CPA, MBA and has been a Performance Management consultant for over 25 years. He is the founder of ExploringTM1 and highly regarded for his experience combining financial management with corporate planning, reporting and analysis. He lives in Sydney with his wife, two of his three children, their cat, Freckles, a bunch of chooks and some fish. John is a sports nut, who played rugby until he was 40, started playing football at 54 and loves being outdoors.

Leave a Reply

Your email address will not be published. Required fields are marked *

Log In