Forum Discussion

MaritzaAcosta's avatar
MaritzaAcosta
Qrew Trainee
2 years ago
Solved

Formula to Calculate percentage based on Checkmarks and dates

Hello,

I need some help. I need to generate a Percentage based on How many times a member joins a meeting per year. I have each attendance as a simple checkmark and then another field for a date. We have various types of meetings some are only like twice a year or the most we have is 11.  Each record is per person so I have dates filled and just if they attended or not.  Is there anyway to make quickbase know Which meeting is the last and make that a number to use to add to a formula to calculate a percentage? How would I write a formula where I could have the total number of Checked Attendance/ by the Maximum number of meetings per year. Like have one record have 7 meetings and the person only attended 5 and have the field with the formula generate 71%. but then the next record have 5 meetings and have only 1 meeting and have the field with the formula automatically generate 20%?

Or does this have to something that Has to be manually done because the records can vary too much?

Have attached screen shot for better visualization. I hope I explained well enough but let me know if you need further clarification. 



------------------------------
Maritza Acosta
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    2 years ago

    The formula would look like this:

    var number MeetingCount =

    Count(

    not IsNull([Date 1]),

    not IsNull([Date 2]),

    etc ...

    not IsNull([Date 11]);

    var number AttendedCount =

    Count(

    [Attended Date 1],

    [Attended Date 2],

    etc ..

    [Attended Date 11]);

    IF($MeetingCount >0, $AttendedCount / $MeetingCount) 

    // don't want to try to divide by zero.



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 years ago

    The formula would look like this:

    var number MeetingCount =

    Count(

    not IsNull([Date 1]),

    not IsNull([Date 2]),

    etc ...

    not IsNull([Date 11]);

    var number AttendedCount =

    Count(

    [Attended Date 1],

    [Attended Date 2],

    etc ..

    [Attended Date 11]);

    IF($MeetingCount >0, $AttendedCount / $MeetingCount) 

    // don't want to try to divide by zero.



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
  • BrianSeymour's avatar
    BrianSeymour
    Qrew Assistant Captain
    2 years ago

    Based on your screenshot, it looks like you may be storing all the data in a single table? I'd suggest creating multiple tables for Meetings and Members. Then, you relate these tables together where one Meeting would have many Members.

    And I don't want to trip you up here … but, consider a third table too, commonly called pivot table or join table in the middle. That would allow you to model this where one Member may have multiple Meetings.

    For example, you'd create a "parent" Meeting record with a date/time and other details (it looks like you store Meeting Type). Then, you add "child" Member records to store who was present.

    And depending on your use case, every Meeting may not be applicable to every Member. So, perhaps you pivot table could be modeled as "Invites" and then it that table you'd store whether that Member was present.

    • One Meetings has many Invites
    • One Invite has one Meeting (Related Meeting)
    • One Invite has one Member (Related Member
    • One Member has many Invites

    Once the three tables are setup and related, you'd create a summary field on the Meetings table to count the number of Invites (from the pivot table) and a second summary field on the Meetings table to count the number of Invites (also from the pivot table) where the "Attendance box is checked." Then, you'd need one more summary field on the Meetings table to divide the two numbers, displaying them as a percentage.

    You could repeat the summary field process from the context of the Members table.  Summarize (count) their respective number of Invites, count Invite Attendance and divide to get their Attendance rates. Then, you'd run your report from the context of the Members table to get the results you are after!

    If you choose to go this route, you have a much more flexible system that doesn't require manual maintenance of messing with formula fields each time a meeting was added nor require extra fields to be added for new meetings, resulting in "wide tables" vs. "tall tables."

    Maybe that helps? Or maybe that's overkill.

    Marks solution would works too, it's just more rigid and maybe that's fine your project.



    ------------------------------
    Brian Seymour
    ------------------------------