# 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

<table><thead><tr><th width="248">Arguments</th><th width="190">Datatype</th><th>Description</th></tr></thead><tbody><tr><td>expression</td><td>Text, Number, Node reference</td><td>The statement to be evaluated.</td></tr><tr><td>value1, value2, … value_n</td><td>Number</td><td>The list of values to be compared against the expression.</td></tr><tr><td>result1, result2, … result_n</td><td>Number</td><td>The result to be returned if the expression matches with the corresponding value in the argument list.</td></tr><tr><td>default?</td><td> Number</td><td>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.</td></tr></tbody></table>

### 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%.

<figure><img src="https://261229348-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FmXNCTvPRjJZj6UunHBgb%2Fuploads%2Fcx9dAoHICUCOsPjPsbZp%2Fimage.png?alt=media&#x26;token=62d056e1-e044-4536-ac8c-7eba34991457" alt=""><figcaption></figcaption></figure>

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

<mark style="color:blue;">**`SWITCH`**</mark>**`(`**<mark style="color:blue;">**`CURRENT_PERIOD_INDEX`**</mark>**`,1,0.15,8,0.15,11,0.15,12,0.15,0.20)`**

The result of the above formula is shown below:

<figure><img src="https://261229348-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FmXNCTvPRjJZj6UunHBgb%2Fuploads%2Frg6cUbGjEUu7poT2Eyf7%2Fimage.png?alt=media&#x26;token=afdaf7fb-51b5-4c86-915a-f5f0d2032c64" alt=""><figcaption></figcaption></figure>

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.

|                                                                     |           |
| ------------------------------------------------------------------- | --------- |
| <mark style="color:yellow;">Target Operating income (year 5)</mark> | $1,00,000 |
| Year 1 estimated unit sales                                         | 100       |

<table><thead><tr><th width="183">Period</th><th width="109" align="center">1</th><th width="118" align="center">2</th><th width="118" align="center">3</th><th width="118" align="center">4</th><th align="center">5</th></tr></thead><tbody><tr><td><mark style="color:green;">Growth%</mark> </td><td align="center"> N/A </td><td align="center">10.0%</td><td align="center">50.0%</td><td align="center">75.0%</td><td align="center">100.0%</td></tr><tr><td><mark style="color:purple;">Unit price</mark></td><td align="center">400.00</td><td align="center">420.00</td><td align="center">441.00</td><td align="center">463.05</td><td align="center">486.20</td></tr><tr><td><mark style="color:orange;">Unit cost</mark></td><td align="center">250.00</td><td align="center">262.50</td><td align="center">275.63</td><td align="center">289.41</td><td align="center">303.88</td></tr><tr><td><mark style="color:red;">Fixed cost</mark></td><td align="center">250000</td><td align="center">257500</td><td align="center">265225</td><td align="center">273181.8</td><td align="center">281377.2</td></tr></tbody></table>

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

<mark style="color:blue;">**`SWITCH`**</mark>**`(`**<mark style="color:blue;">**`CPI`**</mark>**`1,100,2,`**<mark style="color:blue;">**`THIS`**</mark>**`.`**<mark style="color:blue;">**`PERIOD_LOOKUP`**</mark>**`(3,100)`**<mark style="color:green;">**`[Growth%]`**</mark>**`,3,`**<mark style="color:blue;">**`THIS`**</mark>**`.`**<mark style="color:blue;">**`PERIOD_LOOKUP`**</mark>**`(2,100)`**<mark style="color:green;">**`[Growth%]`**</mark>**`,4,`**<mark style="color:blue;">**`THIS`**</mark>**`.`**<mark style="color:blue;">**`PERIOD_LOOKUP`**</mark>**`(1,100)*`**<mark style="color:green;">**`[Growth%]`**</mark>**`,5,(`**<mark style="color:red;">**`[Fixed cost]`**</mark>**`+`` `**<mark style="color:yellow;">**`[Target Operating income]`**</mark>**` ``)/(`**<mark style="color:purple;">**`[Unit price]`**</mark>**`-`` `**<mark style="color:orange;">**`[Unit cost]`**</mark>**`))`**

The result of the above formula is shown below:

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

{% hint style="info" %}
Refer [THIS](https://docs.valq.com/model/formula-functions/static-identifiers/this-me), [CPI ](https://docs.valq.com/model/formula-functions/static-identifiers/current_period_index)and [PERIOD\_LOOKUP](https://docs.valq.com/model/formula-functions/range-functions/period_lookup) functions to understand why and how they are used in the above formula.
{% endhint %}

### 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](https://docs.valq.com/model/formula-functions/range-functions/period_lookup) is used.