CR XI - formula to calculate average

[campia]

Hi,

I need to calculate the average of time spend on the tickets with priority="very high" per month.
In my db I have the fields: creation date and solved date.

How can I do this formula?

 

[dgillz]

Are you grouping on your report? On Priority or on what?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[Madawc]

Group by month, on one or other date. Do a pair of running totals, summing the number of cases and the time spent on cases with priority="very high" and re-setting for each month.

In the month-group footer, divide the sum by the count.

You could also do it by grouping 'very high' cases within the month.

For finding 'time spend', use

DateDiff ("d", {date1},  {date2})

That assumes you are counting days. For 'hours', use "h" rather than "d".

PS. It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

I think you could use one running total that does an average of {@timespent}, using Madawc's datediff formula, with an evaluation formula:

{table.status} = "High Priority"

Reset on change of group (date, monthly). The runnign total belongs in the group footer.

-LB

 

[campia]

ok it works but in my formula "solved - creation" I count Saturday and Sunday too.
How can I avoid it?

 

[campia]

I found the solution:

formula: average
{table.Solved Date} - {table.Creation Date}


formule: real_average --> avoid saturdays and sundays

dim cpt
formula = {@average}
for cpt = 1 to {@average}
if weekday({table.Creation Date}+cpt,2)>5 Then
formula=formula - 1
end if
next

thanks