Discussions

View Only

Calculating the ""most"" used text answer or average of all text-multiple choice answers

• 1.  Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 11-26-2018 21:47
I have child "Task" records that each have a status assigned to the record. I want to calculate the overall status of the Parent "Deliverable" to give an idea of where all the sub tasks level in an overall status.

For example out of 5 tasks 2 are yellow, 1 is red and 3 are green, so the overall status of the deliverable to describe the tasks would equal GREEN.

• 2.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 11-26-2018 21:55
np
You would make a summary field on the Parent to total up the # of Greens, # of Yellows and # of red, in three separate fields.

Then make a formula to calculate the average Task Status.

Post back if you need help with the formula.

• 3.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 11-30-2018 00:26
Hi QuickBase Coach,

Could you please provide me with the formula as well? I will work on the summary fields first.

Thanks,
Aylin

• 4.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 11-30-2018 00:37
I actually think I did the Summary field incorrectly because of what I choose for the "Total of" part of the summary field. I think it's wrong because the total of the records on the screen shot says 6 and not 3. Please advise.

• 5.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 11-30-2018 01:42
The summary field should just be a Count if the number of records. Nothing to do with the Record ID.

• 6.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 11-30-2018 18:51
Let me know when you have the summary fields working.

• 7.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 12-03-2018 19:28
I fixed the summary fields! I didn't change the first section to a specific field, that's where I messed up.

• 8.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 11-30-2018 18:54
Actually, here isa format for a formula

var number Max = Max([# green], [# yellow], [#red])

Case(\$Max,
[#green], "Green",
[#yellow], "Yellow",
[#red], "Red")

• 9.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 12-03-2018 19:33
Hi,

I have just a couple questions on this:

1. Is this all one formula?
2. What does the "var number" mean above?
3. #green, does this need to be the #FieldNameofTotalGreen?
4. Field Type is: Formula - Rich Text, correct?

Thanks!
Aylin

• 10.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 12-03-2018 19:37
1. Yes this is all one formula.

2. The var refers to a formula variable.  it is a useful technique to make formulas more readable.  Here is some help text.  https://help.quickbase.com/user-assistance/formula_variables.html">https://help.quickbase.com/user-assistance/formula_variables.html">https://help.quickbase.com/user-assistance/formula_variables.html

3. Yes, you would put in the name of your field for the total # of green.

4. The formula is formula text , not formula rich test.

• 11.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 12-07-2018 22:11
Hi I'm almost there!
1. I'm getting this error message for the first "MAX" shown below. What does MAX refer to?
">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1766063/RackMultipart20181207-40489-z75ykx-2018-12-07_17-07-18_inline.jpg?1544220532">

2. Just to take this one step ahead while we are talking about it. I will also like to average the formula of the Average Task Statuses to the related parent record, would I make a formula-text field on the parent table for the Averaged child records of the Grand child records?

• 12.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 12-07-2018 22:26
Please post your code too as I cannot correct a screen shot.  It looks like the formula variable is missing the last semi colon.

We might have to rename the formula variable to call it "highest". Maybe Max is a reserved word.

The formula is using a formula variable just to increase the readability of the formula. https://help.quickbase.com/user-assistance/formula_variables.html">https://help.quickbase.com/user-assistance/formula_variables.html">https://help.quickbase.com/user-assistance/formula_variables.html

As for your second question, I  am not understanding your question.  Can you explain with an example.

• 13.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 12-07-2018 23:36

• 14.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 12-07-2018 23:36

var Number Max = Max([Total Green Workstream Status], [Total Yellow Workstream Status], [Total Red Workstream Status])

Case(\$Max,

[Total Green Workstream Status], "Green",

[Total Yellow Workstream Status], "Yellow",

[Total Red Workstream Status], "Red")

• 15.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 12-07-2018 23:50
It needs a semi colin on his first line

var Number Max = Max([Total Green Workstream Status], [Total Yellow Workstream Status], [Total Red Workstream Status]);

• 16.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 12-07-2018 23:59
Yay that worked! Also, how would I change the formula to keep the field blank if there are no related Workstream Status Records?

• 17.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 12-08-2018 04:04
Make this change to blank the result if all zero.

IF(\$Max > 0,

Case(\$Max,

[Total Green Workstream Status], "Green",

[Total Yellow Workstream Status], "Yellow",

[Total Red Workstream Status], "Red")

If([PicExistsFull], "<a target='_blank' href=" & \$URL & ">" & \$Words & "</a>", " ")
)

be sure that the field properties of the the three Totals fields are set to treat blank as zero.

• 18.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 12-07-2018 23:53

• 19.  RE: Calculating the ""most"" used text answer or average of all text-multiple choice answers

Posted 12-08-2018 04:05
OK, now I see your relationship but you will need to explain this with an example

I will also like to average the formula of the Average Task Statuses to the related parent record, would I make a formula-text field on the parent table for the Averaged child records of the Grand child records?