Discussions

Expand all | Collapse all

Formula to divide column by total

mkosek mkosek12-18-2015 21:12

  • 1.  Formula to divide column by total

    Posted 12-18-2015 20:45

    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?



  • 2.  RE: Formula to divide column by total

    Posted 12-18-2015 21:12
    P.S.  # OF INSPECTIONS IS A SUMMARY FIELD


  • 3.  RE: Formula to divide column by total

    Posted 12-20-2015 22:09
    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. 


  • 4.  RE: Formula to divide column by total

    Posted 09-08-2017 18:09
    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!


  • 5.  RE: Formula to divide column by total

    Posted 09-08-2017 19:40
    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.


  • 6.  RE: Formula to divide column by total

    Posted 09-11-2017 19:41
    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?


  • 7.  RE: Formula to divide column by total

    Posted 09-11-2017 19:50
    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]))


  • 8.  RE: Formula to divide column by total

    Posted 09-11-2017 20:35
    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%

    .


  • 9.  RE: Formula to divide column by total

    Posted 09-11-2017 20:53
    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]


  • 10.  RE: Formula to divide column by total

    Posted 09-11-2017 21:07
    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!


  • 11.  RE: Formula to divide column by total

    Posted 09-11-2017 21:13
    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.


  • 12.  RE: Formula to divide column by total

    Posted 09-12-2017 21:20
    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?


  • 13.  RE: Formula to divide column by total

    Posted 09-12-2017 21:31
    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.


  • 14.  RE: Formula to divide column by total

    Posted 09-12-2017 21:37
    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?


  • 15.  RE: Formula to divide column by total

    Posted 09-12-2017 21:46
    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.


  • 16.  RE: Formula to divide column by total

    Posted 09-12-2017 21:49
    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?


  • 17.  RE: Formula to divide column by total

    Posted 09-12-2017 23:31
    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.


  • 18.  RE: Formula to divide column by total

    Posted 09-13-2017 11:31
    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.


  • 19.  RE: Formula to divide column by total

    Posted 09-13-2017 11:31
    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.