Daily parameter report to monthly

[Meloncolly]

Hi folks,

I have set up a parameter report to query our employees table. Based on tblEmp.DateHired and tblEmp.DateDeparted I can count the number of people ppresent on a given date and the number leaving on the same date, by using a parameter field called ReportDate. So, my result is:

ReportDate: 26 sept 04, Headcount: 345, Leavers 3

My problem is trying to then expand this to get a row, per day, for each day in a month. I've tried to use a range vlue for the ReportDate but then my report date doesn't show up. It won't let me group by the parameter value ReportDate either.

The figure I need is a monthly average of headcount. Daily Headcount is currently count(tblEmpID) which meet my record selection criteria for the ReportDate, but I need to SUM the daily headcounts and then get the average of them.

Any suggestions on formulas I could use to step thru each day in a month? Or just go directly to a monthly average headcount?

Thanks in advance!

 

[synapsevampire]

This will give you the numbers for the number of days that you have data for, not all days in the month (these are very different things statistically).

Also it depends on the type of avaerage you seek (mode, median or mean).

If you simply want the average (mean) number of <anything> per day, group by the date field and select for each month as the option.

Use something like the following formula to get the average per month:

distinctcount({Customer.Customer ID},{Orders.Order Date}, "monthly")/
distinctcount({Orders.Order Date},{Orders.Order Date}, "monthly")

-k