Forum Discussion

JenniferJuhasz's avatar
JenniferJuhasz
Qrew Cadet
3 years ago

Formula Query - How to 'Count' return values

Hi,

Two questions, which are probably a very new-user questions, but I'm stumped.  I'm trying to build a summary table that uses formula queries to refer to data in other tables.

Question 1: 
I want to count the number replies in a given field - the value returned is irrelevant, I just want to count the number of non-null values in this field:
var text QUERY = "{7.OAF.'" & [Month] & "'} AND {7.OBF.'" & LastDayOfMonth([Month]) & "'}";

Count(GetRecords($QUERY,"bru6bq226"),72)

There should be around be a range month-to-month from 50 to 150.. with any text in the field, but the system just gives me '2' for each month.  Why?  

Which leads to my second question:
I thought perhaps maybe it was counting the number options available in that field, in this case, the response 2 would be the correct; so I tried to use it in another field where I need to actually count the number of communities represented at our events (between 20 and 40 each month), and it also only returned the value '2' - so that's clearly not what it is counting.  

How do I count the # of unique responses in a field?

Thank you so much for any guidance you all can offer! I'm spinning my wheels and not quite understanding how to do this.

Take care,

Jen

​​

------------------------------
Jennifer Juhasz
------------------------------
  • Try changing Count to Size 


    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • JenniferJuhasz's avatar
      JenniferJuhasz
      Qrew Cadet
      Hi Mark,

      Thank you - that results in a syntax error that I don't understand.
      For the fields that are just 'counting' (there are 11 of those) - I could create a new formula field that makes those all checkboxes rather than their current text entries, in which case size would work and return back to me the variable I need... So, that's one workaround I can think of.

      For the field where I need to count the number of unique communities (for example, 100 events may have 20 communities represented) - size also just returns a syntax error.  Is there another way to go about this?

      Thank you!!

      ------------------------------
      Jennifer Juhasz
      ------------------------------
      • JenniferJuhasz's avatar
        JenniferJuhasz
        Qrew Cadet
        Hi - I was able to answer the first part of my question by 
        1) Converting the text field responses in a field to checkboxes
        2) Summing those values
        and even determined how to sort a bit deeper to confirm registered guests matching those parameters actually attended an event.  That definitely made me happy!

        So - now I just need to figure out how to 'count' the number of unique instances of a community occur within the given time frame.  Is there a formula that will let me do that?

        ------------------------------
        Jennifer Juhasz
        ------------------------------