Forum Discussion

RobSalaj's avatar
RobSalaj
Qrew Cadet
7 years ago

How to count number of active employees by month

I have an integration coming in from our time tracking system. Where each employee can have many time cards in a month. I am looking to find how many employees or a count of employees by month. that have labor hours >0 and not recount each time card. 

I have a summary report created and thought i could do a calculated field that concatenates the employee number, month and year. then if they have more than one record in any given month or year, count them as 1. otherwise 0?

The formula below is not working though and is still counting all records. Can this be done or is there a better way?
Thanks

Count(

If(Count(List("","","",[Employee Number],ToText([Month No]),ToText([Year No])))>1,1,0)

)









Formulas and functions
reports and charts
Tables and fields
  • AnnSwelgin2's avatar
    AnnSwelgin2
    Qrew Member
    7 years ago
    do you have multiple tables? If you have an employee table you could do a summary field within the relationship with those filters you described. Not sure if that is the right (best) way but I would need more info about how your data relates in the tables.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    I'mnot sure if your employees table has a table �above it� such as company or branch or division, so I will assume not. If you do that that you can follow my steps below without needing the extra dummy table.

    Make a dummy table called employee counts and add one record. It will be Record ID of 1.

    Make a relationship to the time cards table and for the reference field, change that related employee counts field to be a formula numeric that calculates to be a 1.

    Then using the new feature, maybe a summary count distinct summary field on time cards to count distict on employee name or employee ID #. Limit that to where Date is during the current month. Call that field EE count CM.

    Duplicate that field and adjust the date range to during 5e previous month. Call it EE count CM-1.

    Duplicate that field and adjust the date Range filter to during the previous 2 months and not during the previous 1 month.

    Call it EE count CM-2.

    Just keep making Making summary fields until you get say 13 of them for the current month and past 12.