SWITCH

A SWITCH statement evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value is returned.

Syntax

SWITCH(expression, value1, result1, value2, result2, ..., value_n, result_n, default?)

Arguments

Arguments	Datatype	Description
expression	Text, Number, Node reference	The statement to be evaluated.
value1, value2, … value_n	Number	The list of values to be compared against the expression.
result1, result2, … result_n	Number	The result to be returned if the expression matches with the corresponding value in the argument list.
default?	Number	The default value to be returned if the expression does not match with any of the values in the argument list. This is an optional argument.

Return Value

Result when the expression matches with value, default in case there was no match.

Remarks

All result expressions and the default expression must be of the same data type.

Examples

1. In the below given table, discount rate is 20% for every month. Consider a scenario in which the node Discount Rate is to be calculated based on the current month. Months Jan, Aug, Nov and Dec should have 15% as discount rate whereas the remaining months should have 20%.

To achieve this, the following formula should be written in the [Discount Rate] node:

SWITCH(CURRENT_PERIOD_INDEX,1,0.15,8,0.15,11,0.15,12,0.15,0.20)

The result of the above formula is shown below:

In the above example, if the current period's index value is 1,8,11 and 12 (which is the case for Jan, Aug, Nov and Dec periods), their corresponding matching result of 15% is returned. For other periods, the default discount rate of 20% is returned.

2. Consider a scenario where Year 1's estimated unit sales value and Year 5's target operating income are given. You need to calculate the unit sales for further periods. Since we have different calculations for each period we can use the SWITCH function to specify the calculation for each period.

Target Operating income (year 5)	$1,00,000
Year 1 estimated unit sales	100

Period	1	2	3	4	5
Growth%	N/A	10.0%	50.0%	75.0%	100.0%
Unit price	400.00	420.00	441.00	463.05	486.20
Unit cost	250.00	262.50	275.63	289.41	303.88
Fixed cost	250000	257500	265225	273181.8	281377.2

This can be achieved by using the SWITCH function in the following manner:

SWITCH(CPI1,100,2,THIS.PERIOD_LOOKUP(3,100)[Growth%],3,THIS.PERIOD_LOOKUP(2,100)[Growth%],4,THIS.PERIOD_LOOKUP(1,100)*[Growth%],5,([Fixed cost]+ [Target Operating income] )/([Unit price]- [Unit cost]))

The result of the above formula is shown below:

Period	1	2	3	4	5
Unit Sales	100	209	1045	1568	2091

Refer THIS, CPI and PERIOD_LOOKUP functions to understand why and how they are used in the above formula.

Explanation

For period 1 the value is 100, for period 5 the unit sales is calculated as (fixed cost + targeted operating income)/(Unit price - Unit Cost). Periods 2, 3 & 4 are calculated as the product of 5th period value and growth percentage. For this period lookup function is used.