Examples of Conditional Tests

This example demonstrates how you can perform conditional tests in Essbase formulas.

You can apply the following formula to a Commission member in the cube outline to calculate commission at 1% of sales if the sales are greater than 500000:

IF(Sales > 500000)
   Commission = Sales * .01;
ENDIF;

If you place the formula in a calculation script, you must associate the formula with the Commission member as shown:

Commission (IF(Sales > 500000)
   Commission = Sales * .01;
ENDIF;)

Essbase cycles through the cube, performing these calculations:

1. The IF statement checks to see if the value of Sales for the current member combination is greater than 500000.

2. If Sales is greater than 500000, Essbase multiplies the value in Sales by 0.01 and places the result in Commission.

In the next example, the formula tests the ancestry of the current member and then applies the appropriate Payroll calculation formula:

IF(@ISIDESC(East) OR @ISIDESC(West))
   Payroll = Sales * .15;
ELSEIF(@ISIDESC(Central))
   Payroll = Sales * .11;
ELSE
   Payroll = Sales * .10;
ENDIF;

If you place the formula in a calculation script, you must associate the formula with the Payroll member as shown:

Payroll(IF(@ISIDESC(East) OR @ISIDESC(West))
   Payroll = Sales * .15;
ELSEIF(@ISIDESC(Central))
   Payroll = Sales * .11;
ELSE
   Payroll = Sales * .10;
ENDIF;)

Essbase cycles through the database, performing the following calculations:

1. The IF statement uses the @ISIDESC function to check whether the current member on the Market dimension is a descendant of either East or West.

2. If the current member on the Market dimension is a descendant of East or West, Essbase multiplies the value in Sales by 0.15 and moves on to the next member combination.

3. If the current member is not a descendant of East or West, the ELSEIF statement uses the @ISIDESC function to check whether the current member is a descendant of Central.

4. If the current member on the Market dimension is a descendant of Central, Essbase multiplies the value in Sales by 0.11 and moves to the next member combination.

5. If the current member is not a descendant of East, West, or Central, Essbase multiplies the value in Sales by 0.10 and moves to the next member combination.

See About Multidimensional Calculation Concepts.