Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Display Second Fastest Truck 1

Status
Not open for further replies.

zidol

Technical User
Feb 9, 2016
24
US
My data consist of records with Incident Numbers. Within these incident records are several trucks with a response time attached to each truck:

Incident Number - 160039
Truck - E34 ____ Response - 2.85
Truck - E35 ____ Response - 2.83
Truck - T34 ____ Response - 6.30
Truck - A35 ____ Response - 3.34

Incident Number - 160423
Truck - E34 ____ Response - 4.21
Truck - E35 ____ Response - 3.55
Truck - T34 ____ Response - 3.19
Truck - A35 ____ Response - 5.62

I need to report on the second fastest truck. For example:

Incident Number 160039
Truck E34 - Response 2.85

Incident Number 160423
Truck E35 - Response 3.55

How can I accomplish this?
 
I achieved it this way.
[ul]
[li]Create group on {Table.Incident};[/li]
[li]Sort records ascending on {Table.Response};[/li]
[li]Create Group Selection formula as follows:
Code:
{Table.Response} <> MINIMUM({Table.Response}, {Table.Incident})
[/li]
[li]Move {Table.Truck} and {Table.Response} from Details section to Group Header section[/li]
[/ul]

Hope this helps.

Cheers
Pete
 
You have not identified what would happen if there was only 1 Truck. My solution would not return a result if only 1 Truck responded.

If you would want to be able to identify incidents where only one truck responded, you would need to use a variable.

Cheers
Pete
 
Hi Pete -

I am trying to get the 2nd fastest truck. I think what you described would give me the 1st truck.

Thanks - Lynn
 
Pete's 'Group Selection' formula would exclude (it would not display) the fastest truck from the sorted list of trucks.
 
zidol, did you at least try my solution?

The Group Selection excludes the fastest truck from the display (as AndyMc states) so the contents of the Group Header now will show the next (2nd) fastest.

Pete
 
I have not tried it - I am having problems with connection to my database. Hopefully I can tomorrow.

Question - what if I want to know the 3rd fastest?

 
Good morning -

I finally got to try the formula and it does work great.
How can I find out what the 3rd fastest truck?

 
This will work:
[ul]
[li]Create Group on {Table.Incident};[/li]
[li]Create Running Total {#RT}: Field to Summarize = {Table.Incident}; Evaluate = For each Record; Type of Summary = Count; Reset = On Change of Group ({Table.Incident});[/li]
[li]Conditionally Suppress Details section using the following formula:
Code:
 {#RT} <> 3
[/li]
[/ul]

In need, you could create a Parameter so the user could enter which truck they wanted to report on. In that case, the Conditional Suppression formula would be
Code:
{#RT} <> {?Param Name}

Hope this helps.

Pete

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top