Discussions

Expand all | Collapse all

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

QuickBaseCoach Dev./Training11-30-2018 18:51

Aylin Nazlim12-07-2018 23:53

  • 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?