Rules - Can't evaluate entered value?

    This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

    • Rules - Can't evaluate entered value?

      It seems that I cannot use an If statement to evaluate an entered value.

      Here is my situation:
      1. I have some formulas set up to calculate next year's budget based on this year's actuals. So I have a formula in the Budget cube which basically says Sales = "2008", "Working Budget" = "2007", "Actual" * 0.5

      2. To make the budgeting process more dynamic, a user can enter an "Override" to manually enter the data instead of using the formula.

      3. So the formula in (1) above becomes something like If field x = "Override", Stet(), "2008", "Working Budget" = "2007", "Actual" * 0.5.

      This all works great. (And thanks, by the way, for the rules engine, they make all the difference!)

      Once these numbers are in the db, I can then perform more calculations on them. For instance, i can have COGS = Sales * 0.1 and this formula would work for either the *calculated values* or the *overriden-entered values*. All is well.

      But the problem seems to be when I try to use an IF statement. For instance, if I put in change the Rule above for the COGS calc to be something like:

      COGS = If Sales > 1000, Sales * 0.1, Sales * 0.11

      the result is a #NULL! for the "overriden-entered values" only. The other values work fine.

      Any thoughts on this? I can post the exact rules and attached spreadsheet if that will help describe the problem. Thanks,

      Mike
    • RE: Rules - Can't evaluate entered value?

      Attached is the exported rules file and a spreadsheet that describes the issue.

      The spreadsheet shows 2 products and 4 measures: Sales Volume, MA1, MA2 and Sales Override. (MA stands for Margin Adjustment).

      The sheet is looking at the Working Budget for 2008. The example shows two products.

      As you can see in the rules:

      1. The Sales Volume amounts are calculated from the previous year's actuals, UNLESS there is an X in the Override (Jan only). If there is an X, the cells are open for input. In the example show, the Sales Volume on the second product is entered (i.e using the override) while the first product is a calc'd on previous year.

      2. MA1 and MA2 are both calculated from Sales Volume.

      3. The calculation of MA1 is successful in either case. Palo can determine the amount of the Sales Volume whether it is calculated or entered, and apply the correct % to correctly calculate the MA1 value.

      4. MA2 first tries to check the amount of the Sales Volume in order to apply the correct percentage. If the value is over 150, apply 15%, else apply 25%.
      It works with no issues if the Sales Volume is calculated from last year's amounts (see MA2 for product 1). BUT, if it is an entered amount for the Sales Volume, the result is #Null!

      The db is too big to attach. (I'll try to squeeze it down)

      thanks for any help with this!

      Mike
      Files