YOY_REL
Category: Compare periods
Overview
The YOY_REL function returns the relative growth for each year compared to the previous year (year-over-year).
Use this function when you need the percentage change between consecutive years.
Syntax
YOY_REL('Node' [, "MissingValueBehaviour"])
Example usage: YOY_REL('Revenue')
Parameters
| Parameter | Description | Type | Required | Default |
|---|---|---|---|---|
| Node | Input node, specified using the node name in single quotes (e.g.'Revenue') | Node reference | Yes | — |
| MissingValue Behaviour | How missing values in the time series are treated | Keyword | No | IGNORE_ MISSING |
MissingValueBehaviour options:
"IGNORE_MISSING": Missing rows are skipped. A result row is only produced where both the current year and the previous year have values. This is the default."MISSING_AS_ZERO": Missing rows are treated as 0.
Output Shape
| Aspect | Behavior |
|---|---|
| Dimensionality | Same as input. |
| Time range | The first year is always dropped (no previous year to compare against). Additional years may be dropped depending on MissingValueBehaviour. |
| Values | Each value is (current_year / previous_year) - 1. The result is a ratio, not a percentage. |
| Row count | Reduced. At minimum, the first year is removed. With IGNORE_MISSING, years adjacent to gaps are also removed. |
Watch Out
- The input must have a Year level. If the input does not contain a Year level, the function fails.
- The result is a ratio, not a percentage: 0.5 means +50%, -1 means -100%.
- Division by zero occurs when the previous year’s value is 0. The affected cell is silently dropped (N/A). This is especially relevant with MISSING_AS_ZERO, where gaps are filled with 0.
- With MISSING_AS_ZERO, the function produces results at years where only the previous year has data, treating the missing current year as 0. This leads to a result of -1 (a 100% decline). Be cautious when interpreting these results.
Examples
Default: ignoring missing values
This example shows YOY_REL with the default behavior.
Input node: Profit
| Year | Value |
|---|---|
| 2025 | 200 |
| 2026 | 300 |
| 2027 | 450 |
| 2028 | 500 |
| 2030 | 100 |
Formula: YOY_REL('Profit') = YOY_REL('Profit', "IGNORE_MISSING")
| Year | → YOY_REL Result |
|---|---|
| 2026 | (300 - 200) / 200 = 0.5 |
| 2027 | (450 - 300) / 300 = 0.5 |
| 2028 | (500 - 450) / 450 = 0.11 |
The first year (2025) has no previous year to compare against. The missing year 2029 causes 2030 to also be excluded because no valid previous year exists.
Treating missing values as zero
With MISSING_AS_ZERO, gaps are filled with 0. This produces results for every year but can cause division by zero when the previous year has no data.
Formula: YOY_REL('Profit', "MISSING_AS_ZERO")
| Year | → YOY_REL Result |
|---|---|
| 2026 | (300 - 200) / 200 = 0.5 |
| 2027 | 150 / 300 = 0.5 |
| 2028 | 50 / 450 = 0.11 |
| 2029 | (0 - 500) / 500 = -1 |
| (100 - 0) / 0 = division by zero results in entry not being present in result |
The missing year 2029 is treated as having value 0, which produces a -1 result (100% decline). Year 2030 attempts to divide by 0 (the previous year 2029 was treated as 0), so the entry is dropped from the result.
Related Functions
| Function | When to use instead |
|---|---|
| YOY_ABS | When you need the absolute difference to the previous year instead of a percentage. |
| DELTA_REL | When you need relative change along any dimension, not just time. |
| CAGR | When you need compound growth over multiple periods instead of a single-period comparison. |