Conditionals on XAE

Generally, a conditional statement or expression allows you to perform different actions based on whether a user-defined Boolean condition is true or false.

The metric definition syntax supports the IF-THEN-ELSE and CASE conditional statements.

IF-THEN-ELSE

Use the IF-THEN-ELSE statement when two options are possible. Based on the evaluation of the IF clause, one of two possible values is returned or one of two possible computations is performed.

Example

You calculate different pricing strategies for the existing and new customers. The pricing model is based on the length of the contract, and every additional Year adds a 1% discount for an existing customer.

The Length metric defines the number of months from the beginning of the contract. The pricing metric Pre-final price sets the price. And finally, the Discount metric computes the value of discount based on the length of the contract.

What:

SELECT IF (Length > 12) THEN (Pre-final price - Discount) 
      ELSE (Pre-final price) END

How: Customer

There is no syntax before the ELSE keyword. The conditional is terminated by the END keyword.

CASE

Use the CASE statement when the final value or computation is possible only if a certain condition is met. 

Example

You arrange a bonus program for the sales representatives. You have 2 departments: Inside sales and Direct sales. You define the Yearly extra achievement bonus services as follows:

  • 5% for all employees in Inside sales department if their amount of won opportunities is over 2.5M
  • 3% for all employees in Direct sales department if their amount of won opportunities is over 4M
  • 1% for all other employees

What:

SELECT Amount WHERE Status=Won
SELECT Payments
SELECT CASE 
   WHEN(Department=Inside Sales 
   AND Amount of Won > 2500000) THEN (Payroll * 1.05), 
   WHEN (Department=Direct Sales 
   AND Amount of Won > 4000000) THEN (Payroll * 1.03) 
   ELSE (Payroll*1.01) 
   END

How: Department, Sales Rep 

The results are as follows: