Rolling average number of active employees

CRV10 - I need to write a report showing a rolling average number of active employees for current year.  I'm thinking I  need formulas for nbr active first day of month, nbr active last day of month and average nbr for the month.  Then somehow I need to have a rolling average.  So if Jan there were 100 active employees the avg would be 100.  If Feb there were 75 active employees on the last day, then the rolling average would be 100 + 75 divided by 2 = 87.5 and so on.  I'm thinking the end result would look something like this:
Avg Emps:  end of Jan    end of Feb    end of Mar   end of Apr ......
It needs to be current year only.  My fields are {EMPLOYEE_DATE_HIRED} and {EMPLOYEE_DATE_TERMED}.
The report will be auto run and emailed monthly so I can't use parameters.  I would appreciate your recommendations.
Who is Participating?
Well, to be honest I don't see any way you can directly create the report with the data that you have available to you.  The problem is that to do what you want in Crystal, you're going to need to group the report by month, and to get that data accurately you will need to have a record for each employee for every month.

So for example if my data is:
Date Hired:  Jan 2007      Date Termed:  Apr 2007

What you would need for your data is:

Jan 2007
Feb 2007
Mar 2007
Apr 2007

Is it possible that you can write a stored procedure on your database to return this information?  If so, then I can help you write the report that will show the results you want.  However, on reflection if you write a procedure to return data this way you might as well write the procedure to just return the number of employees per month:

Jan   100
Feb   75

This could be done using formulas that count the employees and then calculate for the report footer.

jph826Author Commented:
Thank you both.  If I use a count employee formula, how would the rolling average fit in each month?
Assuming you can get the data you need, you would use shared variables to keep your rolling averages.  A formula something like this in the group footer - assuming you group by month:

shared numberVar NumberOfMonths;
shared numberVar TotalEmployees;
numberVar RollingAverage;
NumberOfMonths := NumberOfMonths + 1;
TotalEmployees := TotalEmployees + Count({Table.Employee},{Table.DateField});
RollingAverage := TotalEmployees / NumberOfMonths;

Also you'll want to initialize the variables in your report header:

// @InitFormula
shared numberVar NumberOfMonths := 0;
shared numberVar TotalEmployees := 0;

That should show you the rolling averages in each group footer.
Glad i could help

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.