• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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.
  • 2
  • 2
3 Solutions
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


Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now