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,402 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 ltduran

ltduran

  • 590 Points 500 badge 2x thumb
I followed all of your steps. Once you do the lookup back down to the Inspectors Table, how do you get the %?

I'd really appreciate the help!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,402 Points 50k badge 2x thumb
Well;, if there is a field on the Inspectors table for their # of inspections, it would just be

[# inspections] / [Total Inspections]

and then show that as a % with zero decimals.
Photo of ltduran

ltduran

  • 590 Points 500 badge 2x thumb
This was a great deal of help, I appreciate it. Do you know if there is anyway to get this Total Inspections # to adjust based on filters you might set in a report. For example, if I only want it to apply to inspections between a particular set of dates. Does this make sense?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,402 Points 50k badge 2x thumb
yes, but you will need to set the date range in two new fields on the Inspections totals record, and look up those two dates down to the detail report.  Then use those dates as report filters.  That will control both the details on the report and the count of total inspections.

I suggest that if you do this, that you make a formula field to use as a report break heading  as a Group By  in the report to remind the user of what date range is set.


e.g. 

"<font color = blue>Date Range: <font color=red>" & List(" - "  ToText([Start Range Lookup]), ToText([End Range Lookup]))
Photo of ltduran

ltduran

  • 590 Points 500 badge 2x thumb
Ok I've created the two new fields on the total inspections (Game Totals) record. I've created the look up fields on the detail report. Now I need to use those dates as report filters on the summary report? 

This is changing the details of the report, but not the Total Inspections (Total Games) on the inspections totals (Game Totals) record. This is problematic because any calculations using the Totals are incorrect. In the picture attached you can see that my specific game % ([# of Games]/[Total Games]) is 33.33% for each Game Guide when it should be 50%

.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,402 Points 50k badge 2x thumb
Oh sorry, you will also need to change the summary field for the # of inspections to include those date filters too.

Where [date] >= [Start Range lookup]
Where [date] <= [End Range lookup]
Photo of ltduran

ltduran

  • 590 Points 500 badge 2x thumb
Perfect! This worked! Thanks so much. In regard to the formula field you suggested I create, what do you mean by report break heading?

Thanks!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,402 Points 50k badge 2x thumb
Right, so the user runs the report and they have no idea what the date filters are because they are hidden away on that Games Totals record. how do we show that to the user?

I see now that you are using a Summary Report, (I forgot that it was not a table report), but the same concept can work.

On the summary report first summarize by the Report Break Headings and then by the game guide and then by the Room name.  I suppose the field could be called [Report Dates for Report Display]

You should then be able to see the date filters on that Summary Report.
Photo of ltduran

ltduran

  • 590 Points 500 badge 2x thumb
Got it! So if I'm creating a report, for a specific date range, I will have to go change the date range in my Game Totals record first beforehand.

Is there anyway for those dates to change along with my filters in my report settings?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,402 Points 50k badge 2x thumb
The report needs to be changed to only use the date range from the lookup. It should not be using any other date filters.  That way the report will always match the date filters on that game totals record.
Photo of ltduran

ltduran

  • 590 Points 500 badge 2x thumb
Basically, I need to make a report like this for a number of different date ranges and a number of different stores. So would I need to create a background table (Game Totals) for each different report that I create?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,402 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,402 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,402 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,402 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.