Forum Discussion

WilliamCundiff's avatar
WilliamCundiff
Qrew Trainee
3 years ago

Combine Summary Reports

I have two tables: Employee Leave and Active Directory. Employee Leave records have a Shift Type and Date. Active Directory records also have a Shift Type. The Active Directory can have many Employee Leave records.

I want to summarize the number of Missed Shifts and Available Shifts in one summary report, grouped by week and Shift Type. I also need to add calculated columns like Total Missed %, and Missed % by Shift Type.

This doesn't seem difficult in theory but I can't seem to combine the two. Attached is an excel version of what I'm trying to create.

Thanks in advance!

William



------------------------------
William C
------------------------------
reports and charts

5 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    3 years ago
    You will need to have a table of Weeks where the key field is the date that represents the Sunday of the week.  Use Excel to load up all the Sundays for say the next 10 years.  Set the Key field to be the date field.

    Then make a relationship where One Sunday has many  Employee Leaves.  Let it create a field for Related Week, but then edit that field to make it a formula field 

    FirstDateOfWeek([my date field])

    Then make summary fields on the Relationship to summarize up to Weeks the various fields you need to sum from  Employe Leaves.


    Repeat the process from the other table, Active Directory.  Then you will be able to do your calculations on the weeks table.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • WilliamCundiff's avatar
      WilliamCundiff
      Qrew Trainee
      3 years ago
      Thanks, Mark! I did everything as instructed for the first table. When i tried to add summary fields to the first relationship i received the following:

      We are sorry -- at this time, you cannot create a summary field using a relationship where the reference field is this type of formula field. Please visit our limits page (https://lamresearch.quickbase.com/db/main?act=dohelp&page=quickbase_limits) or contact Care for more information.

      Any idea how to get around this?

      Best,
      William

      ------------------------------
      William C
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        3 years ago
        Can you tell me more about the date field you are using on the child side? is this a data entry field type?  Or a formula field based on Date Created.

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