Display Second Fastest Truck 1

[zidol]

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?

 

[pmax9999]

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:

{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

 

[pmax9999]

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

 

[zidol]

Hi Pete -

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

Thanks - Lynn

 

[AndyMc]

Pete's 'Group Selection' formula would exclude (it would not display) the fastest truck from the sorted list of trucks.

 

[pmax9999]

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

 

[zidol]

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?

 

[zidol]

Good morning -

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

 

[pmax9999]

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:

 {#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

{#RT} <> {?Param Name}

Hope this helps.

Pete