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!
