5 Replies Latest reply: Jun 6, 2012 12:27 PM by Stefan Wühl RSS

    Problem: Expression generating negative numbers

      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)





        • Problem: Expression generating negative numbers
          Christophe Brault



          And if you try :





            • Problem: Expression generating negative numbers

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


              Take care.



                • Problem: Expression generating negative numbers

                  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?

                    • Problem: Expression generating negative numbers

                      ive tried :-



                      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.

                        • Re: Problem: Expression generating negative numbers
                          Stefan Wühl

                          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.





                          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  [