how can I "sum on change of"?

[Andie]

I have a data set where the widgets move between locations. I need to know the time each widget spent in each location, each time...not a sum of the total time spent in a location. The data needs to stay in order by date. An example would be the SubTotals option in Excel, but my data set is huge and I would need to nest at least 3 subtotals. So I'm hoping to do this in Crystal. Below is a simplified version of the data.

V1 V2 FrmLoc FrmDate Time
1 a ABC 9/1 5
1 a ABC 9/1 95
1 a ABC 9/2 785
1 a ABC 9/2 218
1 a DEF 9/3 315
1 a DEF 9/4 881
1 a DEF 9/5 611
1 a ABC 9/5 511
1 a ABC 9/6 480
1 a DEF 9/7 152
2 c ABC 11/4 205
2 c ABC 11/4 263
2 c DEF 11/5 222
2 c DEF 11/5 725
3 d DEF 10/1 153
3 d DEF 10/1 128
3 d DEF 10/2 152
3 e ABC 12/16 88
3 e ABC 12/16 460
3 e ABC 12/16 124
3 e DEF 12/17 451
3 e DEF 12/17 987


I would need to know sum of the time based on changes in V1, V2 and FrmLoc. An example of the output is below (assuming I manually added correctly).

V1 V2 FrmLoc TotTime
1 a abc 1103
1 a def 1807
1 a abc 991
1 a def 1205
2 c abc 468
2 c def 947
3 d def 433
3 e abc 672
3 e def 1438

Any recommendations of the best way to do this? I have CR11, Standard ed.

Thank you!

 

[Bloke152]

your manual example looks like you have grouped by v1, v2, FromLoc and then put in a summary of the time. You could put all the fields together in a formula field and create a group out of that formula field and sum by that group.

 

[Andie]

If I group by v1, v2, and FromLoc, then won't it fail to tell me the individual amount of time per location?

For example, the first widget (v1=1 and v2=a) goes to ABC, then DEF, then ABC, and then DEF. I need to have 2 different totals for ABC and two different totals for DEF. As I understand grouping, it would give me one total for ABC and one total for DEF.

Thanks.

 

[Bloke152]

yeah your right.
im not 100% sure what your after but i have another idea, based on the part where you said 'I would need to know sum of the time based on changes in V1, V2 and FrmLoc.'
you could try adding a formula field that holds v1, v2 and frmLoc and then putting in a running total that sums time and set it to on change of field, your formula field.

 

[lbass]

To expand on Bloke's last suggestion, create a formula {@concat}:

{table.V1}+{table.V2}+{table.FrmLoc}

Then insert a running total on {table.time}, sum, evaluate for each record, reset on change of field {@concat}. Place the running total in the detail section and then go to the section expert->details->suppress->x+2 and enter:

onlastrecord or
{@concat} = next({@concat})

-LB

 

[Andie]

That seems to do the trick! Thanks for the help.