I went to create a rule to calculate some string rules today and I kept getting a rule error saying that I had an invalid expression. Thankfully the solution was pretty easy!
I have a cube where I have an element that indicates if a set of days are to be included in a forecast inventory calculation or not. I could have combined all of this into a complex rule, however I wanted the user to see easily if a day was forecast or not. Therefore in my measures I 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. I am then using the output of that rule to do further calculations.
The issue arose because I was testing if a cell equaled ‘Yes’ or not. I used square brackets to refer to ‘Plan Active?’ and was then getting rule errors when saving. Grrr! I thought.
Fixing it was then rather easy when I worked out how. All I needed to do was use a full DB reference rather than just referring to the element I wanted and bingo!
So I ended up with a rule that looked like this:
# 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) )
, 'No' ) ;
# Zap 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' )
, Stet );
You can see the use of the DB function in the last IF rule directly above.