How to create thresholds in Lens

[erikg]

Hello,

I know this can be done with TVSB:

TVSB Example:

But is it possible to add thresholds to Lens:

The reason I want to use Lens over TVSB is just that in the TVSB example it considers "/" as "empty"

[stephenb (Stephen Brown)]

Hmmm @erikg

Looks like that is not supported but you could do something like this?

[Marco_Liberati]

There are 3 possible outcome to represent a chart with a threshold over it:

• show all bars the same way + add a threshold line
• show the bars below the threshold with color X and the ones above with color Y (like TSVB)
• show all bars below the threshold with color X and the part over the threshold with color Y

Let's go in order.

Show all bars the same way + add a threshold line

This is the simplest solution: create a chart and then add a reference line layer and configure the threshold.

Now let's see the other two options, who require a combination of a stack chart type and the definition of multiple metrics per layer

show the bars below the threshold with color X and the ones above with color Y

This hack leverages the stacked nature of the chart, and it applies the threshold at the metric level.

In the pic above it is possible to see the 2 metrics defined: one for the metric below and another above the threshold.
Both metric have a formula with an ifelse but the 2nd and 3rd arguments are respectively swapped:

• Below threshold formula: ifelse( metric() >= THRESHOLD, 0, metric() )
• Above threshold formula: ifelse( metric() >= THRESHOLD, metric(), 0 )

For instance in the pic above this is the below formula: ifelse( count() >= 150, 0, count() )

show all bars below the threshold with color X and the part over the threshold with color Y

This is an evolution from the version above, I've seen it used from @VinceDS , and this is a nice trick to visualize only the portion above the threshold.

This time formulas as slightly different from the ones above as it needs to subtract the threshold values from the metric one:

• Below threshold formula: ifelse( metric() >= THRESHOLD, THRESHOLD, metric() - THRESHOLD)
• Above threshold formula: ifelse( metric() >= THRESHOLD, metric() - THRESHOLD, 0 )

[stephenb (Stephen Brown)]

@Marco_Liberati TIL! That is awesome with the if else..