Average of Count 1

[Jacob438]

I have a report that displays varies information about bugs found per day. At the report footer, I enter a Summary field that counts how many records (bugs in my case) were found over the reporting period (specified by the user).

Now here's what I want to do:
Create another summary field that tells the average number of bugs found per day over the reporting period. When I try to insert a summary field, it won't let me select a count of a value in order to do an average on. And anyways, I don't see an average value listed in actions, the closest I see is mode (not mean). Which makes me think I may have to write my own formula for this. I am totally lost on how to go about this, can anyone give me some pointers?

Thanks,

Jacob438

 

[goranm]

Hi !

If I get you wright, I think you can write a formula like this and put it in your reportfooter:

Count ({yourField}) / DateDiff("d",minimum({yourDateTime}), maximum({YourDateTime}))

(With the DateDiff you get the number of days between the oldest and newest record)

/Goran

 

[Jacob438]

Oh cool, yeah, that makes sense, I believe that'll work. My thinking about this had become too uptight, I guess that happens when you think about something too hard.

Thanks again,

Jacob438

 

[Jacob438]

Ok, now I've got something a little trickier. Now I just want to track the average over weekdays. Now, I initially I thought I could do something like this:

Count ({yourField}) / DateDiff("w",minimum({yourDateTime}), maximum({YourDateTime}))

But first off, that doesn't return the right value. I know in VB if you use "w" in datediff, it is to return number of weekdays. But the problem is, the total count contains items from the weekends, and I want to exclude them in the total count. Will I need to write a big formula for this? I think my simple idea here is going to end up being quite a bit of code. Let me know if you have any suggestions,

Thanks in advance,

Jacob438

 

[goranm]

Hi Jacob!

Try this in your formula:

DateTimeVar firstday := minimum({yourDateTime});
DateTimeVar lastday := maximum({yourDateTime});
NumberVar loop;
NumberVar weekdays;
NumberVar span;

span := lastday - firstday;

For loop := 0 to span do
(if dayofweek(Firstday + loop) in [2 to 6] then
weekdays := weekdays +1
else
weekdays := weekdays);

Count ({yourField}) / weekdays ;

/Goran

 

[Jacob438]

ok, cool, thanks Goran, I'll try that. I just noticed something, here's the actual formula I used:

Count({table_case.idnumber}) / DateDiff("w",{?StartDate}),{?EndDate})


Is there anything wrong with this? Shouldn't the single "w" return weekdays where as "ww" should return weeks? Or is this incorrect. Thanks for the formula you provided, I may have to use it, but I want to understand why this didn't work in the first place,

Thanks,

Jacob438

 

[goranm]

Hi again !

"w" gives you the number of weeks between the days.

I think you could use the DateDiff like this to get the number of weekdays:

DateDiff("d", {?StartDate}),{?EndDate}) -
DateDiff("ww", {?StartDate}),{?EndDate} , crSaturday) -
DateDiff("ww", {?StartDate}),{?EndDate} , crSunday) +1

/Goran

 

[Jacob438]

so if "w" is to determine number of weeks between days, then what is "ww"? The help files never installed with my Crystal Reports, so sorry if this is documented there.

Thanks for you patience,


Jacob438

 

[goranm]

Hi !

"ww" gives you the number of firstDayOfWeek´s between your StartDate and EnDate.

If you say that firstDayOfWeek is Saturday it counts the number of Saturdays between the two days.
(but it will not count the first day if it´s a Saturday)

/Goran

 

[Jacob438]

oh, that's kind of crappy, but thanks for enlightening me,

Jacob438