Forum Discussion

MichaelMarshal1's avatar
MichaelMarshal1
Qrew Member
6 years ago

Can you create a calculated column in a Summary Report that uses other columns in the report to generate the final column?

I have two columns in a summary report that I would like to generate the third column. The first column is Total Moves, the second column is Total Hours, and I would like the third column to be the first column divded by the second column. I am able to get close by using an average on that calculated field, but what I would really like is for this column to be exactly correct. Any suggestions would be greatly appreciated.
Formulas and functions
reports and charts
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    At this time you cannot do calculations between columns on a Summary report.  The only way to do this would be to create a new table where the Key field was, for example,  the first day of each month going forwards or backwards for a bunch of months.  Then make a relationship back to that table with a formula field like

    FirstDayOfTheMonth([Move Date])

     and Summarize the Total Hours and Total Moves.  Then  do your calculation on that monthly table.

    If you need finer slicing and dicing I have some ideas on that too.
  • AustinK's avatar
    AustinK
    Qrew Commander
    6 years ago
    Does just making one of the columns on the report a formula field and doing the calculations in there not work for this? I thought using a calculated column to add up other columns on the reports worked too, I am almost positive I have summary reports like that.

    It's possible I am missing something obvious here though. It sounds like you want something pretty simple, a column that will divide column 1 by column 2 and have the result in the 3rd column. Is that right?
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      The issue is that if you have a record with 1 defective widget out of 2 produced that is a 50% failure rate, and if there is another batch (record) of widgets with 100 items and 1 failure that is a 1% failure rate.

      If you average the failure rates then the average would be a 25% failure rate  (Averaging 1% and 50%)  when in fact its much closer to a 1 % failure rate for all widgets produced.  So an Average of Averages is not mathematically correct as it needs ot be a weighed average.
    • AustinK's avatar
      AustinK
      Qrew Commander
      6 years ago
      That makes sense. I was thinking more on a per record basis.