New in the Community? Get started here

Schneider Electric Exchange Community

Discuss and solve problems in energy management and automation. Join conversations and share insights on products and solutions. Co-innovate and collaborate with a global network of peers.

Register Now
Resource Advisor - Performance Analytics Forum
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Commander

Calculated expressions: using IF(ISNULL()... to test for missing data

As mentioned at the end of

Filling-gaps-in-one-meter-using-another-meter-s-data

 

there is a not-so-great feature of the IF(testExpression, answerIfTrue, answerIfFalse) function that might not give you what you expect.

 

The IF() function will continue to output values for as long as there is data coming from its first operand, the logical test.  If you are using ISNULL(<expression>) as the first argument to IF(), then it will run out of data when the stream being tested in the ISNULL() runs out of data, and the whole thing will quit outputting values, when really you'd normally want ISNULL() to return true after its operand runs out of data.

 

So, if you use

 

IF(ISNULL({RAMetric(Water volume)}),1,0)

 

you might be disappointed to find that it simply quits when that RAMetric() runs out of values, rather than switching from outputting 0's to outputting 1's. 

 

THE FIX:

To get around this, the argument to ISNULL needs to be turned into an expression that will still output values  even when the original data runs out.  The easiest way to do that is to add a 'do-nothing' operation, like multiplying by one.  It's hardly intuitive, but if you modified the above expression to be:

 

IF(ISNULL({RAMetric(Water volume)} * 1.0 /* added *1 to get values when the metric runs out */),1,0)

 

you'll find that it switches from outputting 0 to outputting 1 past the end of the last value in the metric. That comment is just for show, it's not needed to make the expression work.  Also, the values in the second and third argument can be any expression, I've just used numbers to make it easy to read.