Conditionals
Choose between two results with if, then, and else, and build tests with comparisons, and, or, not, and in.
A conditional gives one result when a test is true and a different result when the test is false. Use it for thresholds, start and end dates, seasonal months, and start values.
1. Write the conditional
Use this form:
if <test> then <result> else <result>The else part is necessary. Without it, the formula bar shows Expected "else".
if Revenue > 100000 then Revenue * 0.05 else 0The Conditional flag template inserts if {condition} then 1 else 0.
2. Compare values
| Operator | True when |
|---|---|
= | The values are equal |
<> | The values are not equal. != has the same meaning |
< | The left value is less |
<= | The left value is less or equal |
> | The left value is greater |
>= | The left value is greater or equal |
A comparison gives 1 when it is true and 0 when it is false. You can use that number directly:
(Revenue > 100000) * Bonus poolA value can also be the test. A value that is not 0 is true. 0 is false.
You cannot chain comparisons. Write Price > 10 and Price < 20, not 20 > Price > 10.
3. Join tests
| Keyword | Result |
|---|---|
and | True when both tests are true |
or | True when one test or both tests are true |
not | Reverses the test that follows |
Eigenn applies not first, then and, then or. Use parentheses to make the order clear.
if timeStep >= Start step and (timeStep <= End step or End step = blank) then 1 else 0Start step and End step are variables that hold time steps. For example, Start step can use the formula date(2027, 1). The formula gives 1 from the start step onward. It stops after the end step, or continues when End step is blank. The Active in period template inserts this form.
4. Test a list of values
Use in with a list in square brackets. Use not in for the opposite test.
if month in [11, 12] then Holiday uplift else 1if month not in [6, 7, 8] then Base hours else Summer hoursmonth is a built-in helper. It gives the calendar month of the period, from 1 to 12.
5. Test more than two cases
Put a second if after else. Press Shift+Enter in the formula bar to put each case on its own line.
if Revenue >= 150000 then 0.10
else if Revenue >= 100000 then 0.05
else 0Eigenn checks the tests from top to bottom. It uses the first test that is true.
6. Know how blank values behave
=and<>treatblankas a value.if End step = blank then ...works.<,<=,>, and>=with a blank value give a blank result.- When the test is blank, the whole conditional is blank. Eigenn uses neither result.
orgives true when one side is true, also when the other side is blank.andgives false when one side is false, also when the other side is blank.
To use a start value in the first period, test for blank:
if Customers[previous] = blank then Starting customers else Customers[previous] + New customersThe Blanks and helpers page explains when a value is blank.
7. Test the period or the item
- Use the helpers
month,quarter,year, andtimeStepto test the period. - Use
lastActualDateto separate actual periods from forecast periods:if timeStep <= lastActualDate then ... else .... - In a variable that is broken down by a dimension, test the item:
if Region = Europe then ... else .... The Dimension references page explains this test.