Blanks and helpers
How blank values calculate, how the Blanks in data setting works, and the built-in helper values that every formula can read.
A blank value is no value. It is not the same as 0. Helpers are built-in values, such as month and timeStep, that every formula can read without a variable. Use this page when a first period, a missing input, or a date test gives an unexpected result.
Blank values
A value is blank in these cases:
- A variable has no value in that period.
- A time reference points before the first period, and the variable uses Treat as blank.
- A conditional has a blank test.
- The formula gives
blankornoneas a result.
blank is a keyword. none is a helper with the same empty value. Both work in a test:
if End step = blank then 1 else 0How blank values calculate
| Formula, where Cash is blank | Result | Rule |
|---|---|---|
Cash | Blank | A blank stays blank |
Cash * 2 | 0 | In arithmetic, a blank counts as 0 |
avg(Cash, 4) | 4 | avg skips blank values |
Cash = blank | 1 | = and <> compare blank as a value |
Cash > 1 | Blank | <, <=, >, and >= give blank |
if Cash > 1 then 5 else 6 | Blank | A blank test makes the whole conditional blank |
The Blanks in data setting
Each variable has a Blanks in data setting. It sets the value that a formula reads when a time reference points outside the model. An example is Customers[previous] in the first period.
- On the variable row, select the options button (⋮).
- In the Blanks in data list, select one option.
| Option | A reference outside the model gives |
|---|---|
| Treat as blank | A blank value. A = blank test is true |
| Treat as zero | 0. A = blank test is false |
| Error | No value. The formula gives no result in that period, and a = blank test does not work |
The setting belongs to the variable that the formula reads. To use a start value in the first period, set Treat as blank on the variable, and test for blank:
if Customers[previous] = blank then Starting customers else Customers[previous] + New customersHelpers
| Helper | Value |
|---|---|
month | The calendar month of the period, from 1 to 12 |
quarter | The calendar quarter of the period, from 1 to 4 |
year | The calendar year of the period |
fiscalmonth | The month of the fiscal year, from 1 to 12 |
fiscalyear | The fiscal year of the period. Blank when Use fiscal years is off |
timeStep | The position of the period, counted from 0. t is a short form |
numTimesteps | The number of periods in the model |
today | The position of the period that contains the current date |
lastActualDate | The position of the last actual period |
daysinmonth | The number of days in the calendar month of the period |
daysInPeriod | The number of days in the period |
weekdaysInPeriod | The number of days from Monday to Friday in the period |
cohort | The position of the current cohort, counted from 0, in a model with a cohort dimension |
e | Euler's number, 2.718… |
none | A blank value |
Helper names are not case-sensitive. They are reserved, so a formula cannot read a variable that has a helper name. The Formula syntax page explains this rule.
Examples
A bonus in December only:
if month = 12 then Annual bonus else 0A daily rate that follows the length of each month:
Daily cost * daysinmonthA Revenue plan that copies Revenue up to the last actual period, then grows by 2% each period:
if timeStep <= lastActualDate then Revenue else Revenue plan[previous] * 1.02