Formula to divide column by total

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

I am trying to add a column to the attached summary table that will show the % of inspections an operator is having compared to total inspections being done.

# of inspections / Total inspections

Since I do not have a field for Total inspections I am not sure how to accomplish this. Is there a way to incorporate Quickbases Total field into a formula?

Photo of mkosek

mkosek

  • 876 Points 500 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of mkosek

mkosek

  • 876 Points 500 badge 2x thumb
P.S.  # OF INSPECTIONS IS A SUMMARY FIELD
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,472 Points 50k badge 2x thumb
There is not a way to use a Totals field in a formula for calculations.  The only way really is to have another table which will create the totals that you need.  

For example,

You make a new table called called Inspections Totals and make exactly 1 record in it.  It will be record ID#1.  Then on the Inspectors Records, you make a field called [Link to Summary Totals]  as a numeric-formula field with a formula of 1.  


The make a Relationship where 1 Summary Totals record is related to many (in fact all) Inspectors records.  Then do a summary total on that Relationships on Total Inspections.  I assume that you might have different Summary totals fields on the Inspectors table for different time periods such as Last Month, This Month To Date, this YTD etc.  So there would also be different Summary fields on the Summary table. 


Then do a lookup back down to the Inspectors table and you will be able to get your calculation of the % of inspection that a particular Inspector has done. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,472 Points 50k badge 2x thumb
That was the first time I knew that this also needed to work for different Stores.  I don't think that this approach will work in that situation.
Photo of ltduran

ltduran

  • 590 Points 500 badge 2x thumb
Ok got it, well let's just assume that I only need it for one store. Is it possible to alter a report's date filters and have my data reflect those dates, when it comes to my totals percentage calculations?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,472 Points 50k badge 2x thumb
The Game Total record would need to have a selection for the store. (new relationship where 1 Store has many Game Totals)   Then the report filter would need to filter based on the lookup of that store as well.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,472 Points 50k badge 2x thumb
Itduran,

Sorry I just realized that for your situation there is a way way easier solution.

Just make a formula numeric percentage field the formula like

IF([Win] = true, 1,0)

Then add that field to your summary report and set it to show as an average.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,472 Points 50k badge 2x thumb
Itduran,

Sorry I just realized that for your situation there is a way way easier solution.

Just make a formula numeric percentage field the formula like

IF([Win] = true, 1,0)

Then add that field to your summary report and set it to show as an average.