String Rules

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!

Situation

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.

Problem

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.

Solution

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)  ) 

, 'Yes' 

, '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' )

, Continue 

, Stet );

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

If you like this post, please spread the love…

About John

Managing Director of Infocube. We are a management accounting consultancy that specialises in Business Analytics.

This entry was posted in Rule Examples. Bookmark the permalink.