Subtract previous field value from group of records

[capronton]

I have grouped by bus and want to subtract the first record in the group from the
first record in the next group. Even when I move the fields from detail to the
Group Header and the duplicate records are not visible the previous function
continues to subtract the non-visible records. Any ideas on this would
be much appreciated.

For example, in the first record below for bus 5011 I want to subtract
5:13:54 - 4:58:54

For bus 5018 I want to subtract
5:31:54 - 5:13:54

For bus 5008 I want to subtract
5:43:55 - 5:31:54

For bus 5009 I want to subtract
5:58:55 - 5:43:55

Can someone explain how to do this?

incLogID tDate incTime bus
5011
148430872 2/23/16 4:58:54 5011
5018
148432058 2/23/16 5:13:54 5018
148432530 2/23/16 5:18:54 5018
148432610 2/23/16 5:19:54 5018
5008
148433982 2/23/16 5:31:54 5008
5009
148435548 2/23/16 5:43:55 5009
148435682 2/23/16 5:44:55 5009
148436396 2/23/16 5:49:55 5009
148436682 2/23/16 5:51:55 5009
5017
148437758 2/23/16 5:58:55 5017
5010
148440910 2/23/16 6:14:55 5010
5021
148442678 2/23/16 6:22:55 5021
5015
148445796 2/23/16 6:36:55 5015
5007
148449412 2/23/16 6:51:55 5007
5013
148452618 2/23/16 7:04:56 5013
148452900 2/23/16 7:05:56 5013
148453120 2/23/16 7:06:56 5013
5022
148455376 2/23/16 7:15:56 5022
148458510 2/23/16 7:27:56 5022
5014
148458522 2/23/16 7:27:56 5014
148460280 2/23/16 7:33:56 5014
148460526 2/23/16 7:34:56 5014
148462072 2/23/16 7:39:56 5014
148462668 2/23/16 7:41:56 5014
148462952 2/23/16 7:42:56 5014
148463278 2/23/16 7:43:56 5014
5001
148462080 2/23/16 7:39:56 5001

 

[capronton]

I used the "minimum" function to get the minimum "incTime" for the bus group. See formula below:
Formula for @minArrival - Minimum ({incident_log.incident_date_time}, {incident_log.vehicle_id})

When I try to subtract the @minArrival as shown below I get the error
"The field has no previous or next value"

DateDiff ("n", previous({@minArrival}),{@minArrival})


Can someone explain why I can't subtract "@mnArrival? See Source Data below.

incLogID tDate minArrival incTime bus
5011
148430872 2/23/16 4:58:54 4:58:54 5011
5018
148432058 2/23/16 5:13:54 5:13:54 5018
148432530 2/23/16 5:13:54 5:18:54 5018
148432610 2/23/16 5:13:54 5:19:54 5018
5008
148433982 2/23/16 5:31:54 5:31:54 5008
5009
148435548 2/23/16 5:43:55 5:43:55 5009
148435682 2/23/16 5:43:55 5:44:55 5009
148436396 2/23/16 5:43:55 5:49:55 5009
148436682 2/23/16 5:43:55 5:51:55 5009
5017
148437758 2/23/16 5:58:55 5:58:55 5017
5010
148440910 2/23/16 6:14:55 6:14:55 5010
5021
148442678 2/23/16 6:22:55 6:22:55 5021
5015
148445796 2/23/16 6:36:55 6:36:55 5015
5007
148449412 2/23/16 6:51:55 6:51:55 5007
5013
148452618 2/23/16 7:04:56 7:04:56 5013
148452900 2/23/16 7:04:56 7:05:56 5013
148453120 2/23/16 7:04:56 7:06:56 5013
5022
148455376 2/23/16 7:15:56 7:15:56 5022
148458510 2/23/16 7:15:56 7:27:56 5022
5014
148458522 2/23/16 7:27:56 7:27:56 5014
148460280 2/23/16 7:27:56 7:33:56 5014
148460526 2/23/16 7:27:56 7:34:56 5014
148462072 2/23/16 7:27:56 7:39:56 5014
148462668 2/23/16 7:27:56 7:41:56 5014
148462952 2/23/16 7:27:56 7:42:56 5014
148463278 2/23/16 7:27:56 7:43:56 5014
5001
148462080 2/23/16 7:39:56 7:39:56 5001

 

[CrystalizeCanada]

Try:

WhilePrintingRecords; //Place in Group Footer
if {incident_log.vehicle_id} <> Next({incident_log.vehicle_id}) then 
(
Time(Next({incident_log.incident_date_time})) - 
Time(Minimum ({incident_log.incident_date_time}, {incident_log.vehicle_id}))
)/60 
else 0;

Gordon
Crystalize

 

[capronton]

Excellent!!! This works great, thanks!

 

[capronton]

Can't believe this. The Group is not allowing me to sort in the original order even though I chose the "original order" option.

How can I sort the fields without the Group affecting the "original sort"?
How can I my original "record sort" and prevent the "group sort" from changing the "record sort"?

 

[CrystalizeCanada]

The filter may be affecting the order in which the records are returned.
Remove the filter and see if the report goes back to 'original sort' order by the group.
If it does go back to the order you want without the filter you may want to to take the filter out and use a Saved Data Filter instead of a Record Filter.

[pre]Report, Select Expert, Saved Data...[/pre]

Keep in mind that using Saved Data may negatively impact the speed of the report.

Gordon
Crystalize

 

[capronton]

I have to totally remove the "Group By Vehicle" to go back to the original "sort order". Removing filters does not help.

Any other suggestions to retain my original "record sort", while including the group?

 

[CrystalizeCanada]

[pre]Database, Select Distinct Records[/pre]
also seems to cause the 'original sort' to be lost (on my sample database).

If neither Database, Select Distinct Records nor the Record Selection Formula are the culprit then I would experiment with a copy of the report and strip virtually everything out to see if you could identify what is causing it to ignore the 'original sort'. If you cannot identify anything consider starting a new thread for this topic and see if anyone has any other ideas.

Gordon
Crystalize