Forum Discussion

AylinNazlim's avatar
AylinNazlim
Qrew Cadet
7 years ago

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

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. 
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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.
  • AylinNazlim's avatar
    AylinNazlim
    Qrew Cadet
    7 years ago
    Hi QuickBase Coach,

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

    Thanks,
    Aylin
  • AylinNazlim's avatar
    AylinNazlim
    Qrew Cadet
    7 years ago
    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. 

  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    The summary field should just be a Count if the number of records. Nothing to do with the Record ID.
    • AylinNazlim's avatar
      AylinNazlim
      Qrew Cadet
      7 years ago
      I fixed the summary fields! I didn't change the first section to a specific field, that's where I messed up. 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    Actually, here isa format for a formula

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

    Case($Max,
    [#green], "Green",
    [#yellow], "Yellow",
    [#red], "Red")
    • AylinNazlim's avatar
      AylinNazlim
      Qrew Cadet
      7 years ago
      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
    • AylinNazlim's avatar
      AylinNazlim
      Qrew Cadet
      7 years ago
      Hi I'm almost there!
      1. I'm getting this error message for the first "MAX" shown below. What does MAX refer to?


      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?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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]);
    • AylinNazlim's avatar
      AylinNazlim
      Qrew Cadet
      7 years ago
      Yay that worked! Also, how would I change the formula to keep the field blank if there are no related Workstream Status Records?
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      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.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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?