Hi, any help would be appreciated with this.

I have records of jobs i do for customers, I am trying to find a way to work out the average days between each job we do ( to predict when they will need us next). At present I have a list of jobs with dates i.e.

1/1/10 Purchased 500 gallons

7/1/10 Purchased 500 gallons

18/1/10 Purchased 600 Gallons

etc...

What I am after is something like:

1/1/10 Purchased 500 gallons

7/1/10 (6 Days) Purchased 500 gallons

18/1/10 ( 11 days) Purchased 600 Gallons

The date used is the creation date of the job record.

Many thanks for looking.

Well, if you want to work out the average days between jobs, I believe you could do this directly by:

18/1/10 - 1/1/10 = 17 days;

3 jobs - 1 = 2 intervals in-between jobs;

17 days / 2 intervals = 8.5 days on average.

Or, in more general terms (pseudocode)

Max ( Date ) - Min ( Date ) / ( Count ( Jobs ) - 1 )

Edited September 13, 201015 yr by Guest

Hi

Thanks for that, but I need to know the days between each date so that I can see trends and patterns which occur during different seasons.

For that, you will need to define a self-join relationship of the Jobs table, such as:

Jobs::Date > Jobs 2::Date

with records on the Jobs 2 side of the relationship sorted by Date, descending. Then you can calculate the interval as =

Date - Jobs 2::Date

It would probably be better to lookup the previous date into a local field (using the same relationship) in order to speed things up.

Thanks very much Michael, that sorted it !