Forum Discussion

NoreenJustice's avatar
NoreenJustice
Qrew Trainee
6 years ago

Formula Based on Specific Text Value within the Same Field

My records have a field called Department and a multiple choice field called "Status". The status choices are Approved, Rejected, New, In Review. I would like to get a total of the records with Approved and Rejected only then divide the Approved by that total to get Percent Approved.  I don't want the percentage based on all existing records. I will need this calculation to produce reports by Department. I wasn't sure whether I should create a new field to hold this or use a report formula. Either way I need assistance with the formula itself.
#formulas #reportsandcharts



------------------------------
Noreen Justice
------------------------------
​​​
formulas
reports and charts
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    If your department field is just a multiple choice field, then the best solution by far will be to create a table of departments and set the Key field to be the department name.

    Then create a relationship where 1 Department has many Records and for the reference field on the right, select your existing Department field.

    Then make some Summary fields, for example # Approved in last 90 days, and # Submitted for approvals in last 90 days.  Then do the division to get the % up on the Department record.  And make a report on the Department table.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------
    • NoreenJustice's avatar
      NoreenJustice
      Qrew Trainee
      6 years ago
      Worked like a charm! Also provided me with additional flexibility for reporting that I wasn't expecting so...bonus! Many thanks

      ------------------------------
      Noreen Justice
      ------------------------------