Discussions

Expand all | Collapse all

Track Active Employees by Day

  • 1.  Track Active Employees by Day

    Posted 04-07-2020 11:38
    I have a table which lists employees and a formula field which determines if they are currently active based on their start and end date. Any suggestions on how I could generate a table or report that keeps record of the number of active employees each day?  Thanks!

    ------------------------------
    Mary Gaughan
    ------------------------------


  • 2.  RE: Track Active Employees by Day

    Posted 04-20-2020 20:18
    Hi Mary!

    What you're referring to is commonly referred to as a snapshot table or report. (not to be confused with the snapshot field option)

    Quick Base has outlined this in a blog post here: https://community.quickbase.com/browse/blogs/blogviewer?BlogKey=4df369e1-76fc-42da-87d2-90419820d890

    Let me know if you find this helpful ๐Ÿ˜Š๐Ÿ‘

    ------------------------------
    Sharon Faust (QuickBaseJunkie.com)
    Founder, Quick Base Junkie
    https://quickbasejunkie.com
    ------------------------------



  • 3.  RE: Track Active Employees by Day

    Posted 12 days ago
    Thanks, Sharon! This worked great. Do you happen to know if there is any other way to do this through calculations instead of snapshots? Just thinking (1) the snapshot table will have a ton of records and (2) if someone is unable to enter a new hire or departure in time, it may miss the snapshot. No worries if not.. this solution is great and I really appreciate the help! -Mary

    ------------------------------
    Mary Gaughan
    ------------------------------



  • 4.  RE: Track Active Employees by Day

    Posted 12 days ago
    Mary,
    If you just was to create 1 record per day of the total number of active employees, I would suggest the following.
    I would set up a new table called employee daily record total. This would be a helper table that would just have one record in it. So you create that one record and then lock down anybody else for Adding records. 

    The record ID will be record ID number one. Then you would create a relationship back to your employees table using a formula reference field of a formula numeric equal to a formula of 1.

    Then you would make a summary field of the number of active employees.  We will also need to have a helper field called [Today] with a formula of Today().

    OK so that gives you the active employee count for today, but how do you create the history?
    What I would then do is have a new table called employee count history. It will have two tables. The date and the total number of employees. 

    Then create an automation to run either seven days a week or five days a week which would copy the value of that summary table and copy the value of today into the date field and obviously the total number of active employees into the employee account table.  


     


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Track Active Employees by Day

    Posted 11 days ago
    This worked! Thanks so much, Mark!

    I now have headcount by any given day, but do you happen to know of a way to track cumulative employees by year? I could have 80 employees on any given day, but the cumulative yearly total employed most often lands around 100.

    My set up is an Employees table with basic info like name then a second table for Positions. The positions have start and end dates. These two tables are connected via a relationship.

    ------------------------------
    Mary Gaughan
    ------------------------------



  • 6.  RE: Track Active Employees by Day

    Posted 11 days ago
    Do you just want a report of the total # of unique employees in a given year? or do you need to see the rate that they built up at?

    If you just want a count of the Unique employees, then just make a summary report of that history table and group by employee.  There will be a count at the bottom of the report.โ€‹

    if you want to see a cumulative "waterfall" chart, that is possible too.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Track Active Employees by Day

    Posted 11 days ago
    Thank you for the quick reply. I'm looking for total # of unique employees in a given year. The history table doesn't have employee names. It just has 1 record per day with the # of active employees.
    โ€‹โ€‹

    ------------------------------
    Mary Gaughan
    ------------------------------



  • 8.  RE: Track Active Employees by Day

    Posted 11 days ago
    Oh, right, you are.
    I think we can just make a report off the employees table.  We probably will need tow reports, one for the current year and one for the last year. The reason for that is that no doubt on January 1 someone is going to ask the question about how many active employees we had last year.

    I think it would be useful to create a field called [first day of the current year] with the formula of FirstDayofYear(Today()) and similarly [last day of the current year] with the formula of LastDayofYear(Today())


    The the report filters would be off the employees table for
    [start date] <   [last day of the current year] 
    and

    indented
     ANY
    [end date] is equal to blank
    [end date] >    [first day of the current year] 



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------