Rolling average number of active employees

Posted on 2007-07-23
Last Modified: 2012-05-05
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.
Question by:jph826
    LVL 42

    Accepted Solution

    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

    LVL 100

    Assisted Solution

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


    Author Comment

    Thank you both.  If I use a count employee formula, how would the rolling average fit in each month?
    LVL 42

    Assisted Solution

    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.
    LVL 100

    Expert Comment

    Glad i could help


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now