I want to multiple one numeric field by 70. Then divide a second numeric field by that answer. Then make it a percentage. When I do it, the answer is not right.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I've created a separate column in the summary table "Calculated Column" to get this answer in a percentage form.  It keeps giving me a wrong answ

(1*70)/25,799 and it gives me 1.5%.  Should be .2%

Photo of John

John

  • 0 Points

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
It is not possible for  QuickBase to do mathematical functions using different "columns" of a summary report.  What in fact is happening when you make a custom field is that the field is being created at the detail level and as a result when you try to summarize it by averaging it's results you were not getting a proper weighted average summary.

I think we should take a step back and if you can describe your business situation and what you're actually trying to calculate  there may be a solution still possible.
Photo of John

John

  • 0 Points
I have a complaint for a job which I was trying to equate to footage by multiplying it by 70 to get a number.  Then I wanted to see what the percentage is for complaints per footage that was completed during a week's time frame.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
If you want to get accurate % calculations, the only way would be to import a table of Sundays from Excel into a Table called Weeks.  Make the Key field of that Weeks table to be that Sunday date field.

Then in your details table have a formula date field with the formula FirstDayOfWeek([complaint date])

Then make a relationship to the Weeks table based on that field and then make summary fields to count the number of complains and the number of footage and do your division in that summary field.
Photo of John

John

  • 0 Points
I already have the Weeks taken care of.  My issue lies with multiplying my Complaints column/field by 70 to get a new number in which I could cross reference/divide into the Total footage completed for that week.  Looks like I need to create new fields first then pull it all together.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
if you are saying that you have the Weeks taken care of simply by having a Summary Report group by Week, that is not going to solve your problem.
Photo of John

John

  • 0 Points
Dang.
I have my footage dated which in turn is tied to my "Date Received" field.  I created a new field to eliminate my issue with Complaints x 70 and it worked.  Then I tried again to divide my Total Footage by my new field Complaints By Footage.  Percentage still didn't come out right.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
:) right, you need to make a new Table of Weeks to Summarize up to, then do your division there.  Having done that, you could then lookup the correct answer down to the details tale and show it as an average and it will give you your existing report, but with a correct calculation.
Photo of John

John

  • 0 Points
Ok. So a new Table not a new Report (Reports & Charts) correct?  Any other way around that?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Yes, there is no other way at this time other than a new table.
Photo of John

John

  • 0 Points
Wait.  Can't I create the fields in an existing Table then pull it into a summary?
Photo of John

John

  • 0 Points
I already have 18 Tables!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
The point is that you need to have a table where each record is a Sunday.  if you do not already have such a Table, then you need a new table.
Photo of John

John

  • 0 Points
My Tables are based off of Job Numbers - that's the main Key.  Time frames are based on Daily Work Reports (which is another Table).  We also keep track of Work Completion Dates.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Was that a question?
Photo of John

John

  • 0 Points
Sorry, no.  Just letting you know how and what I am tracking.  So having a Table where each record is a Sunday.. I'm not sure if that will work in my case.  I was trying to figure out how to correlate that to my work.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
When you had your summary report which was grouping by week, what date were you using?
Photo of John

John

  • 0 Points
I was using the Date Received (the date the complaint was received) and then combine by Week.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
The that is the date field that you will need to use n the FirstDayOfWeek formula to relate to the Weeks table.
Photo of John

John

  • 0 Points
How would that formula go?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
You would summarized the # of complaints received during the week up on that Weeks Table.  Then summarize the footage that week. Then divide those two numbers.
Photo of John

John

  • 0 Points
The summarized items would be actual fields not reports correct.  Then I would create a Summary Report?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Create a Table Report in the Weeks Table
Photo of John

John

  • 0 Points
Ok.  Thanks!  I'll get to work.