SWITCH
Last updated
Last updated
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.
SWITCH(expression, value1, result1, value2, result2, ..., value_n, result_n, default?)
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.
Result when the expression matches with value, default in case there was no match.
All result expressions and the default expression must be of the same data type.
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.
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
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:
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.
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.
