Problem: Expression generating negative numbers

[#1]

Good Morning, I have a problem where I am trying to extract meter readings from our system. This example is an approximation of the data generating the problem.

READ     DATE

297         01.04.2012

9998       01.03.2012

The true difference between the two figures is -9701. However the true system difference is 299, the meter rolls back to 0 when it goes past 9999. Our system calculates this automatically but im unable extract this as its a calculated table (or structure)

I am using this Expression at the moment (which is giving the erroneous data) :- FirstSortedValue(Meter_Reading,-Meter_Reading_Date)-FirstSortedValue(Meter_Reading,-Meter_Reading_Date,3)

Thanks,

Paul.

[christophebrault · Accepted solution]

Hi,

And if you try :

fabs(FirstSortedValue(Meter_Reading,-Meter_Reading_Date)-

(FirstSortedValue(Meter_Reading,-Meter_Reading_Date,3)-9999))

[#3]

Christopher, Thank you very much for that! It's so simple when I look at it. It nailed the spot.

Take care.

Paul.

[#4]

One minor problem I have, this works well when negative numbers when a number starts before and including 9999 and then rolls over to 0+, the problem is when the number moves from 350 to 375 for example, I need the formula in the expression to deal with both, any ideas please?

[#5]

ive tried :-

if(FirstSortedValue(Meter_Reading,-Meter_Reading_Date)-FirstSortedValue(Meter_Reading,-Meter_Reading_Date,3)<'0',

fabs(FirstSortedValue(Meter_Reading,-Meter_Reading_Date)-

FirstSortedValue(Meter_Reading,-Meter_Reading_Date,3)-9999)) which helps for the neg numbers, I need help to get this expression to deal with positive numbers 0 and greater to be calculated in same expression.

[swuehl]

Well, I think you can just reuse your original expression for the 'standard case', can't you?

Or create a aggregated READ value in the script like

LOAD *

, rangesum(if(READ<peek(READ),10000),peek(AggrREAD),READ-peek(READ)) as AggrREAD

INLINE  [

READ,     DATE

9998,       01.03.2012

297,        01.04.2012

350,        01.05.2012

8000,        01.01.2014

100,        01.01.2015

2222,        01.02.2016

0,            01.03.2020

];

So you don't need to bother about crossing the 9999 border anymore after.

Regards,

Stefan

edit: this needs an ordered table to work correctly, and if you want to start with your first READ value and not zero as AggrREAD, you could use:

LOAD *,

if(isnull(peek(READ)), READ,rangesum(if(READ<peek(READ),10000),peek(AggrREAD),READ-peek(READ))) as AggrREAD

INLINE  [

...