NonZero Function

The NonZero function can be used to find when values are not equal to zero. This is particularly useful when looking at boolean conditions where true results have a value of one and false a value of zero.

Here’s how the NonZero function works. In this example the middle Show View panel shows the Pivot Value labels when they occur, separated by a period of zero values because there were no new pivots. The bottom panel shows the same pivot values nested in the NonZero function, which plots the last value every day, ignoring the zeroes, until the next pivot occurs:

This makes it easier to compare previous nonzero values using an offset. In this example, we want to find the high prices when the previous pivot highs occurred. The previous high value can be used with the ValueWhen function:

VALUEWHEN(HIGH(),PIVOT(MIN=10, TYPE=High))

To get the value of the previous pivot highs we use the nonzero function with an offset and conditional IF statement:

//Get value of pivot high
V1 = VALUEWHEN(HIGH(),PIVOT(TYPE=High, MIN=10));
//Compare V1 pivot high with the previous day's value. If it's the same give a value of 0, else V1;
V2 = IF(V1 == V1[1], 0, V1);
//Get the previous value [1] when V2 wasn't 0 i.e. when the pivot value changed;
NONZERO(V2)[1]

In this example for BXB, the watchlist columns match the pivot labels:

Save and open the workbook attached.