Formula to divide column by total
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 Si...
Forum Discussion
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.
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.
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]))
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]))
