Differentiating between NULL and 0 in Calculated Metrics - The LENGTH operator

In theory, 0 and NULL seem very similar, but in practice they represent very different states. For example we might have a daily login count custom metric: a count of 0 means there were no logins at all, while NULL indicates that we're missing the datapoints altogether.

Planhat's custom metrics reflect this: they take a value when a value is sent in, and they don't take a value when no values are sent in. Conversely, calculated metrics always have a value: they will never produce a NULL output, even if the input value (for example, from a custom metric) is NULL.

This is especially problematic for Health scores, where missing raw values still impact on health because they are recoded as 0 values by the calculated metric.

To work around this, producing calculated metrics with outputs which differentiate between NULL and 0 values, you can use the LENGTH operator.

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?

Let's assume, as an example, that we have a product usage custom metric counting number of logins per day. NULL would then mean the product is not purchased, or active at all, whereas 0 means there have been 0 logins.

This means:

• we can't treat 0's as NULL values

• we 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 custom metric NULL values to take negative number identifiers (e.g., -1) in the resulting calculated metric, and non-null values to be calculated from the custom metric as intended (from 0 and upwards).

["IF", 

# taking the custom metric 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 from the custom metric 
{"type": "rawNumber", "value": -1}, 

# setting any non-NULLs in the custom metric to take a calculated value e.g., average over 10 days 
{"type": "metricOverTime", "days": 10, "op": "AVERAGE", "prop": "metrics.Custom Company Metric"}]

📌 Note: you can replace "-1" here with any numeric identifier for a NULL value (e.g., -999). However, keep in mind that these datapoints will show up in your subsequent data visualisations, introducing some error.

Use Case: Health Scoring

The most common use case for this is to prevent NULL values from custom metrics (0 length values) to feed into health scores. By default, NULL custom metric values become 0s when processed into calculated metrics, meaning that the health score is negatively impacted, when it shouldn't be impacted at all.

To resolve this, and prevent any disruption to your charts, we recommend:

1. Making a copy of the base (built on top of the custom metric) calculated metric feeding into your health score (and renaming it appropriately for easy identification)

2. Editing the base calculated metric to include an IF function as above, which assigns an arbitrary "tagging" value (such as -999) to NULL values

3. Bounding the health score to exclude these values (e.g., a lower bound of -998 if the health score should account for negatives, but exclude the NULL values)

👑 Pro-tip: if your health score does not need to reference negative values, then you can avoid the need to build copies of your calculated metrics by setting NULLs to very small negative values (e.g., -0.001), which will minimise their impact on charts and calculations, while preventing them from affecting your health scores.