formula to total then divide field

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • In Progress
I have what seems like should be an "easy" problem to fix.  I just cannot figure it out.  But I know someone has ran into it before so I reach out to the oh-so intelligent community :)

I have a summary table that totals columns from two different tables.  See below:

Name                Total Completes    Total Hours           Column I need help with
So-and-So                 10                        80                                  ***(should be Ttl Hrs / Ttl Comp.)

**The Total Completes and Total Hours Columns are summing individual daily numbers in the summary report.  The last column I have as a calculated column - it should divide the Total Hours by the Total Completes -- which it does fine per day.  But in the summary report (or the total at the bottom of a table report) it just sums up the calculated column, rather than dividing the total hours by the total completes.

Does anyone understand?? Can anyone help???

 
Photo of Stephanie Lane

Stephanie Lane

  • 120 Points 100 badge 2x thumb

Posted 1 month ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
Yes, I understand, but at present you cannot do mathematic calculations across columns in a summary report.  However, in your specific case you may be lucky.  I suggest making an Average  as opposed to a Total of that calculated column and it might work for you.
Photo of Stephanie Lane

Stephanie Lane

  • 120 Points 100 badge 2x thumb
HI!  Good to see you again - you have helped me with like 30 other questions (when I was with another company) 

Anyway, I tried the averages thing, and the number is not quite right.. is there a way to do it with a 3rd table that you could walk me through or should I just go against my grain and combine these two tables into one?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
If the records being summarized are all weighted equally then the Averages will work.  But if they are entries for different quantities, then it will not work. 

In that Case, you can create a table of names and have a relationship to summarize the detail data up to the summary table to get your totals.

Now that we have Automations it may be easier to keep that table updated, ie to auto create the missing records if child records get created and they have no parent.
Photo of Stephanie Lane

Stephanie Lane

  • 120 Points 100 badge 2x thumb
That is an idea - but I think my problem is.... I need the users to be able to look at different date ranges / periods of time - at BEST I would be creating summary fields for each week starting in April and would have to continue to do so each week.  and x2 for both of the fields. Unless I am thinking about it wrong, or there is another way to do it??  This is CRAZY, we need to be able to show this number on reports and it just seems impossible.  
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
Do you just need to summarize by week or is it by like product by week?  If its just by week, then you load up a table with all the Sundays for the next 10 years, using excel.

The alternative to to populate the summary table by manually copying a summary report which creates a concatenated Key field of the Summary you need to do, like YYYYMMDD-Name
Photo of Stephanie Lane

Stephanie Lane

  • 120 Points 100 badge 2x thumb
Well it could be by week, or by month ... or by day I suppose.  And they want to be able to choose the date range they are viewing.  So if it's a report showing grouped by week, then it would only show the weeks they choose in a filter.  

So daily, obviously my number is correct - when I start trying to group or summarize by week or by month, that is when the number doesn't calculate properly.  and it is MADDENING at this point.  OMG so freaking easy!!! We are trying to AVOID excel and promote the use of QB and things like this are exactly why people avoid it and keep emailing version 2389 of a spreadsheet that has grown to 934,012 records - some of which may or may not have been updated in the last version.

and just FYI - the numbers can change - so i'll be updating them probably weekly (So it's not like I can get several weeks worth of #s figured out in excel and then just import them into a table... the calculation needs to be performed and WORKING directly in the report.  There HAS To be a way to do this!!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
I see two solutions.

Why don't you load up a table with all the weeks for the next 10 years and all the months for the next 10 years and all the days for the next 10 years.

Then make your summary totals and make pretty charts or summary reports.

The second solution is to have a control record, which in its simplest form if there are few concurrent users is just a single record in a table which is record ID# = 1 and make that related to all details record.  The the user edits that records for the start and end date.  Those dates flow down to the detail records and are used in he Summary fields which do the totals back up to your control record. 

So if the users need to see trends over time they look at the pretty charts or less pretty summary tables.  If they need a specific date range they enter those dates.

There is also a way to do this so users don't trip over each other if there are many concurrent users setting the date range on that single control record.
Photo of Stephanie Lane

Stephanie Lane

  • 120 Points 100 badge 2x thumb
Mark,

Thank you so much for your help so far - can you give me an example, or point me to an example of using the single record .. i've seen it described before, but I really want to finally understand it and make it work :)
Photo of Stephanie Lane

Stephanie Lane

  • 120 Points 100 badge 2x thumb
OK so yes, I still need help with this concept..... 
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
With a single record you create s new table and add 1 Record. Maybe the table would be called

Set Analysis Period.

It will be Record ID# 1.

Then on the details table make a field called [link to set dates] with a formula numeric formula of

1

Make a relationship to the single record table based on that field, and lookup the dates down to the detail records.

You can then use those lookup fields in you summary totals fields where you are summarizing total hours and total completes.