# 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
• Question
• Updated 3 years ago

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%

• 0 Points

Posted 3 years ago

• 0
• 72,206 Points 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.
• 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.
• 72,206 Points 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.
• 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.
• 72,206 Points 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.
• 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.
• 72,206 Points :) 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.
• 0 Points
Ok. So a new Table not a new Report (Reports & Charts) correct?  Any other way around that?
• 72,206 Points Yes, there is no other way at this time other than a new table.
• 0 Points
Wait.  Can't I create the fields in an existing Table then pull it into a summary?
• 0 Points
• 72,206 Points 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.
• 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.
• 72,206 Points Was that a question?
• 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.
• 72,206 Points When you had your summary report which was grouping by week, what date were you using?
• 0 Points
I was using the Date Received (the date the complaint was received) and then combine by Week.
• 72,206 Points The that is the date field that you will need to use n the FirstDayOfWeek formula to relate to the Weeks table.
• 0 Points
How would that formula go?
• 72,206 Points 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.
• 0 Points
The summarized items would be actual fields not reports correct.  Then I would create a Summary Report?
• 72,206 Points Create a Table Report in the Weeks Table
• 0 Points
Ok.  Thanks!  I'll get to work.