Formula to select max based on a condition

[niku]

<b><br><font color=magenta><br>2 tables customer and customer activity<br>cstomer activity has <br>id, aty_typ, time<br>1&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;jul 10 13:00<br>1&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;july 10 14:00<br>1&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;july 11 12:00<br>1&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;july 11 15:00<br>1&nbsp;&nbsp;&nbsp;&nbsp;4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;july 14 15:00<br>assume aty_typ 1 is walking and 3 is jogging<br>and 4 is running<br>I am trying to create a formula for each customer i need to find the elapsed between the last time they jogged to them beginning to run.&nbsp;&nbsp;Some customers may not have begun jogging or could have gone straight to running.<br>I tried maximum(fld,condfld)<br>timevar x:=maximum(time,aty_typ=3) <br>it doesn't work.&nbsp;&nbsp;Hoe do i get a handle on max time if it exists for aty_typ 3 and then i can subtract that from the min time for aty_typ 4.&nbsp;&nbsp;Any help appreciated. thanks, Niku<br></font><br></b>

 

[niku]

My report should produce summary info as follows<br>customer time exercised<br>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5 hours <br>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3 hours<br>etc and I should be able to exclude certain activities from the total time spent on exercising.<br>Thanks,<br>Niku

 

[MalcolmW]

Your data example seems to be incomplete - you have an ID, an activity, and a time stamp.&nbsp;&nbsp;There is no indication whether the time stamp is a starting time or an ending time for the activity.&nbsp;&nbsp;If you added a field to indicate whether it were a starting time or ending time, the problem would become trivial, assuming every activity had both a starting time and ending time. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[kctmen]

I am trying to get a report to print out the information which goes along with the most current date in a certain field. What would the formula look like that would enable me to do this? <br><br>Thanks