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
NULLvalueswe can't allow
NULLvalues 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"}]