Filling gaps in one meter using another meter's data
Colin Bucknell ran into an interesting situation with a customer. Their meter data is imported from a service that provides two streams - one for data as soon as it's recorded, then a time delayed stream of the same data following validation.
Generally, the time delayed stream is the one you want to see, but while you're waiting for the verification to take place (which can be up to a week) you want to show the unvalidated data.
In the screenshot below (I've obscured some of the names) the validated stream is on a meter called 'main' and the unvalidated data is on 'check'. You can see that the main stream runs out before the check one...
To get a stream at the parent that shows the main when available and check when main runs out, we attached this formula to the parent's energy measurement:
When used without a "PER <time>" clause, the FIRST() function runs through its comma-separated parameters left to right and stops at the first one that's not null. In this case that means using energy from the [Main] meter if it's available, otherwise it falls through to [Check].
Though you would naturally expect this to behave the same way as:
there is a subtle 'gotcha' with IF. It turns out that the IF(testExpression, answerIfTrue, answerIfFalse) function quits producing output when the true/false test expression runs out of values. The ISNULL([Main][Electricity:ENERGY]) test in that first argument stops producing values when the main energy runs out and the whole expression quits, which defeats the purpose in this case.
The FIRST() function works differently - it will keep going as long as any of its arguments has a value, which is what we wanted here.