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

[#1]

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.

    MAX(NomEG)

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!

NanoStickCalculations:

Load

[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 ))

        ,0

        ) as SecondsOfHaul

// Start of Reverse

,if(ReverseRequest - Previous(ReverseRequest) = 1

    ,1

        ,0

        ) as ReverseBinary

// Start of Forward

,if(ForwardRequest - Previous(ForwardRequest) = 1

    ,1

        ,0

        ) as ForwardBinary

// Rev Time    

    ,If(ReverseRequest  = 1

,RangeSum( 1, Peek( SecondsOfReverse ))

        ,0

        ) 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 ))

        ,0

        ) as SecondsOfForward

Resident NanoStick

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

NanoStickCalculations2:

Load

[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,

    [DT]

, peek(BodyUpStartTime)

        ) as BodyUpStartTime

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

Date(([DT])-(5/(24*60)))

, peek(FiveMinStartTime)

        ) as FiveMinStartTime

Resident NanoStickCalculations

Order by [Truck Date Time] desc;

NanoStickCalculations3:

Load

[Truck Date Time]

    ,HID as HID2

    ,RB as RB2

    ,FB as FB2

    ,Timestamp(DT2)

    ,RID as RID2

    ,FID as FID2

,if(RB = 0

    , 0

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

    ,1

        ,0

        )

    ) as RevFiveMinBinary

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

    ,1

        ,0

        ) as FwdFiveMinBinary

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

    ,[DT2]

        ,0

        ) as RevFiveMinTimes

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

    ,[DT2]

        ,0

        ) as FwdFiveMinTimes    

Resident NanoStickCalculations2

Order by [Truck Date Time] asc;

NanoStickCalculations4:

Load

HID2

    , 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;