0 Replies Latest reply: Aug 4, 2017 2:58 PM by Randall Lau RSS

    Determining the max value of a field, not globally, between two date fields that are not static

    Randall Lau

      Hello.  Long time listener, second time caller.


      My question:

          - I am trying to find the maximum of the field NomEG between:  [FirstRevDateTime] <= [Date Time] <= [BodyUpStartTime]

        • I tried a set analysis, but the global filtering caused it to 'work', but not as intended. 

      Thank you for any guidance you are willing to offer!


      I have a table with the following dimensions & measures:



      I am trying identify the max value of a field.



      However, I want that number for each HaulID.  The table does that.


      I want the max NomEG from the last five minutes of a haul AND after the first reverse.  Another way to say this is a truck will drive around an unknown amount of time.  I only care about the last portion of the haul, the last five minutes.  Then, I want to review what is happening once the driver ! starts backing up.


      The problem I have run into is the first reverse in the last five minutes moves.  To locate where that was, I used this FirstSortedValue to find FirstRevDateTime.




      That worked fine for identifying my 'FirstRevDateTime'.  However, using that as a range definition point continues to allude me.

          - I am trying to find the maximum of the field NomEG between:  [FirstRevDateTime] <= [Date Time] <= [BodyUpStartTime]

          - It seemed like an easy next step...



      I tried using Set Analysis:

          aggr(min({$<[Date Time] = {">=$(=max(FirstRevDateTime))"},ReverseRequest={1},ForwardRequest={0}>}NomEG),HaulID)

        •           After I took the time to get this working, I learned that ">=$(=max(FirstRevDateTime))" functions globally
        •           So, while it 'worked', it will only give me the correct value on my max(FirstRevDateTime).


      So, I come seeking wisdom.  Is there another way to do this?

        • Is there a way to manipulate that set analysis equation to look for the FirstRevDateTime in a manner that is not global?
        • I think FirstSortedValue or RangeMax or RangeSum are tools I can use to accomplish this, but I haven't been able to identify the logic to get those to work.


      Any guidance would be greatly appreciated.  I have pasted the main portion of my script below.



      On another note...I am a noob at this.  I am aware I am likely not using Table to Table relations correctly.  If you have any insights on ways to improve my table to table calculations, I would welcome inputs.

      • Should I be using DropTable?
      • Is my use of Resident tables incorrect?




      Thank you for any guidance you are willing to offer!






      [Truck Date Time]

          ,[Dump_Body_Indication] as DBI

          ,Timestamp([Date Time]) as DT

      // Number of Hauls Counter:  Body Down to Body Up From 1 to 0, 0-1

      ,If((Dump_Body_Indication - Previous(Dump_Body_Indication) = -1)

          OR (TruckNumber - previous(TruckNumber)) <> 0

        ,RangeSum( 1, Peek( HaulID ))

        ,Peek( HaulID )


          as HaulID  // HaulID appears to be working correctly.  Confirmed both DBI and TN equations work



      //(((( Add total haul time )))))





      // Single Haul Time - Confirmed Demo    

          ,If(Dump_Body_Indication - Previous(Dump_Body_Indication) <> -1

      ,RangeSum( 1, Peek( SecondsOfHaul ))


              ) as SecondsOfHaul










      // Start of Reverse

      ,if(ReverseRequest - Previous(ReverseRequest) = 1



              ) as ReverseBinary



      // Start of Forward

      ,if(ForwardRequest - Previous(ForwardRequest) = 1



              ) as ForwardBinary




      // Rev Time    

          ,If(ReverseRequest  = 1

      ,RangeSum( 1, Peek( SecondsOfReverse ))


              ) as SecondsOfReverse



      // Number of Rev Counter:  Start of Rev

      ,if(ReverseRequest - Previous(ReverseRequest) = 1,

          RangeSum( 1, Peek( ReverseID ))

              ,Peek( ReverseID )

              ) as ReverseID



      // Number of Fwd Counter:  Start of Fwd

      ,if(ForwardRequest - Previous(ForwardRequest) = 1,

          RangeSum( 1, Peek( ForwardID ))

              ,Peek( ForwardID )

              ) as ForwardID


      // Fwd Time    

          ,If(ForwardRequest  = 1

      ,RangeSum( 1, Peek( SecondsOfForward ))


              ) as SecondsOfForward


      Resident NanoStick

      Order by [Truck Date Time] asc;  // Order by appears to be working.







      [Truck Date Time]

          ,ReverseBinary as RB

          ,ForwardBinary as FB

          ,Timestamp(DT) as DT2

          ,HaulID as HID

          ,ReverseID as RID

          ,ForwardID as FID



      ,if(DBI - Previous(DBI) = 1,


      , peek(BodyUpStartTime)

              ) as BodyUpStartTime



      ,if(DBI - Previous(DBI) = 1,


      , peek(FiveMinStartTime)

              ) as FiveMinStartTime



      Resident NanoStickCalculations

      Order by [Truck Date Time] desc;





      [Truck Date Time]

          ,HID as HID2

          ,RB as RB2

          ,FB as FB2


          ,RID as RID2

          ,FID as FID2

      ,if(RB = 0

          , 0

              ,if([DT2] >= FiveMinStartTime //AND [DT2] <= BodyUpStartTime




          ) as RevFiveMinBinary

      ,if(FB = 1 AND [DT2] >= FiveMinStartTime //AND [DT2] <= BodyUpStartTime



              ) as FwdFiveMinBinary

      ,if(RB = 1 AND [DT2] >= FiveMinStartTime //AND [DT2] <= BodyUpStartTime



              ) as RevFiveMinTimes

      ,if(FB = 1 AND [DT2] >= FiveMinStartTime //AND [DT2] <= BodyUpStartTime



              ) as FwdFiveMinTimes    


      Resident NanoStickCalculations2

      Order by [Truck Date Time] asc;






          , Timestamp(FirstSortedValue(DISTINCT DT3,RevFiveMinBinary=1)) as FirstRevDateTime

          , FirstSortedValue(DISTINCT RID2 ,RevFiveMinBinary=1) as MinReverseID

          , FirstSortedValue(DISTINCT FID2 ,RevFiveMinBinary=1) as MinForwardID


      Resident NanoStickCalculations3 Group By HID2;