Basic IF functions in Sigma

1. Basic IF functions in Sigma

Welcome back. Let's get started with one of the most useful functions in Sigma - the If function.

2. Using IF functions for conditional logic

When you're working with data, sometimes you need to calculate columns based on simple yes/no logic. For example, you might need to flag rows of data that meet a numeric threshold, or surpass a given date deadline. The conditional logic behind If functions lets Sigma decide the answer for each row, based on your rule.

3. Two new functions

In this video, we'll talk about the IF and SWITCH functions for performing this type of conditional, row-level logic.

4. DEMO

Let's imagine Oakmark Bank wants to add a column to their transactions table flagging transaction amounts over $1000. The If function requires a logical condition that will either be true or false. We'll write [transaction amount] > 1000. Next, we'll provide the result that should show up in the column when this is true - we'll add the word "large". We could stop here and any transaction $1000 or less would remain null, or we could specify an "else" condition, such as the word "small". That was a simple If statement with 2 possible outcomes, but what if we have more than 2 conditions? Perhaps we'd like to expand our previous example to include a middle range giving transaction amounts between $500 and $1000 the label "medium". To do so, we add the second condition after the first condition's true result is specified. In more complex if statements like this, we keep the first [If] and [then] arguments, but then we layer in additional pairs of [If]/[then] arguments until we use a final else condition to capture all remaining values. The final argument will classify all remaining transactions as small. We aren't limited to 3 conditions - we can expand the if function to include many more conditions. Just exercise caution - deeply nested If statements can be hard to interpret and debug. In those cases, it might be worth breaking complex If calculations across multiple calculated columns. In some scenarios, particularly where you're trying to swap out one text value for another, map values to labels or set custom categories, another type of If function called Switch can be a big timesaver. The Switch function is like giving Sigma pairs of values for mapping one value to another without needing to write the full "If" syntax for each option. For example, to simplify the naming convention of these Transaction types, we could use the Switch function to swap the words Withdrawal with the letters "Wd", Transfer with "Tf", and Deposit with "Dp". We could then provide a final [else] condition to label any remaining transaction type "Other", or we could skip the [else] argument to leave all other rows null. It's important to note that the Switch function, while easy to write, works best when swapping discrete values - it's not suitable for numeric ranges or approximate matches - Switch only matches exact values. Functions like If and Switch are powerful because they allow business users to quickly apply real business logic to rows of data in their Sigma workbooks.

5. Let's practice!

Now let's try a few exercises to practice with basic, row-level If and Switch functions.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.