Generally, a conditional statement or expression allows you to perform different actions based on whether a user-defined Boolean condition is
The metric definition syntax supports the
CASE conditional statements.
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.
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.
SELECT IF (Length > 12) THEN (Pre-final price - Discount) ELSE (Pre-final price) END
There is no syntax before the
ELSE keyword. The conditional is terminated by the
CASE statement when the final value or computation is possible only if a certain condition is met.
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
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: