Average Difference between several dates 1

[aloittelija]

I'm working with Crystal 8.5 and have a problem of creating a formula which would calculate an average time distance between list of dates. The problem is that the list consists just values of dates not any particular order. For example:

Product Date
a 12-Feb-02
a 1-Apr-02
b 31-Dec-02
c 31-Jan-03
c 28-Feb-03
b 1-Mar-03
a 1-Mar-03


So for example I would have to calculate for product a the average difference between dates 12-Feb-02 to 1-Apr-02 and 1-Apr-02 to 1-Mar-03.

Any help?

 

[dgillz]

Not sure what you are trying to do....why does your second set of dates end in 1-mar-03 rather than just the next record in your list? Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[synapsevampire]

If you can group by Product and sort by date, this is easy, if you need to display them in this order, then this will involve a subreport.

-k

 

[aloittelija]

More information:

Let's say these dates are the dates when the product has been sold and I need to count the average date between purchases for each product.

I just can not see that grouping and sorting would give me the possibility to calculate this? In the end I'm not interested to show these individual dates just calculate the average date between them by products.

Any help?

 

[Madawc]

If I've understood you correctly, you want to find the intervals between several different dates, and then the average of these intervals.

You are up against a fundamental feature of Crystal, the 'hump' where it does most of its calculations. So that if you created several formula fields to do the individual calculations of difference, Crystal would refuse to 'see' those fields for the purpose of avergaging them.

I can't suggest any easy way round, unfortunately. Lumping the assessment and averaging in a single Formula Field might do the trick. If you also need the date differences, calculate these separately in other Formula Fields.

If this fails, you might have to get very indirect. Create a subreport to find the various differences. Pass back the number and total difference. And then in a lower section, use these values to get your average. Madawc Williams
East Anglia
Great Britain

 

[aloittelija]

One more question:

Thanks Madawc. I thought it might be difficult.

But I got already lost when trying to do the calculation of the difference between the dates.

Any help?

 

[Madawc]

There is a DateDiff function, but I've not used it much. You can decide on the interval, years or months or days. Madawc Williams
East Anglia
Great Britain

 

[aloittelija]

More questions:

My problem is that the date difference should be calculated from the same field. Not that I have the begining date in another and the end in another.

Please see the begining statement!

Any help please!

 

[Madawc]

You can check Previous(myfield) or Next (myfield) to get the prior record or the next record. I've never tried it with dates but it should work. Madawc Williams
East Anglia
Great Britain

 

[aloittelija]

Thanks! Worked!