Calculated metrics always result in a value: there's no such thing as a NULL
calculated metric output, even though there can of course be NULL
inputs. But there are cases where we need to differentiate between missing (NULL
) values, which should have no impact on the resulting metric and zeroes (which should have a negative impact on the metric).
That's what the LENGTH
operator allows for.
What is it?
The LENGTH
function is an operator, just like LAST
, SUM
, MAX
, MIN
, and so on. It has one simple but powerful job: calculating the length of an array, such as [A, B, C, D] or [1, 2, 3, 4, 5]. In the first case, the operator would return 4, and in the second case, 5.
How can you use it to differentiate NULL
and 0?
As an example, NULL
means that the product is not in use at all, 0 means that the product's usage is extremely low.
This means:
we can't treat 0's as
NULL
valueswe can't allow
NULL
values to have any impact at all on the calculated metric
We can achieve this by combining LENGTH
with IF
, setting NULL
values to take negative number identifiers (e.g., -1) in the calculated metric, and non-null values to be calculated as intended (from 0 and upwards).
["IF",
# taking the length value (calculating the length of the array)
{"type": "metricOverTime", "days": 10, "op": "LENGTH", "prop": "metrics.Custom Company Metric"},
# setting the IF condition: "if the length is equal to 0"
{"type": "condition", "value": "equal to"},
{"type": "rawNumber", "value": 0},
# assigning negative values to NULLs
{"type": "rawNumber", "value": -1},
# setting any non-NULLs to take a calculated value e.g., average over 10 days
{"type": "metricOverTime", "days": 10, "op": "AVERAGE", "prop": "metrics.Custom Company Metric"}]