11 Replies Latest reply on Dec 29, 2008 2:15 AM by raybaudi

    Totalling value list responses in a Calculation field

    trevorlawson

      Title

      Totalling value list responses in a Calculation field

      Post

      Hi all,

       

      As a newbie, please forgive my ignorance on Calculations.

       

      In my questionnaire database, respondents have given an answer to a question.  The answer options are contained in a value list:

       

      Very Poor

      Poor

      Reasonable

      Good

      Very good

       

      So if the question was "What do you think of my Calculation writing skills", most will have selected "Very poor" from the drop down list. 

       

      I have 360 respondents and want to show how many said "Very poor", "Poor" and so on, in a report.  I want to write a calculation field for each possible answer (five in all).

       

      I've created a first calculation field for answer "Very poor" and cannot get the field to produce a number, because I'm getting syntax wrong.

       

      The field containing the value list is called "QA07".  What should the calculation look like?  I think it should look something like this, but the syntax is way out somewhere. 

       

      SUM ( If QA07 = Very poor )

       

      The Specify Calculation dialogue box tells me that The specified field could not be found.

       

      What I find a bit weird about Filemaker is that there are no operators on either side of a field to make it clear that it is a field, as is the case in, say, Excel.

       

      One other thing.  Is Filemaker case-sensitive?  The reason that I ask is that one possible answer is "Very poor" whilst another is "Poor".  If I ask Filemaker to calculate how many said "Poor", will the answer also include those who chose "Very poor", even though it has a lower case "p"?

       

      Any advice warmly welcomed.

       

      Cheers,

       

      Trevor 

       

        • 1. Re: Totalling value list responses in a Calculation field
          Orlando
            

          Hi Trevor

           

          Your on the right track but you need to split the process of getting to the total into two fields, the first field you need to setup is what I call an Alert field, a calculation that puts a 1 in a field for a specific option, and the a Summary Total field that totals all your Alerts. 

           

          So for the question you describe you would setup the following:

           

            AlertQA07_VeryPoor ( Calculated Number ) 

           

              If ( QA07 = "Very Poor" ; 1 ; "" )

           

          And then second field to total all the records, TotalQA07_VeryPoor, Set this up by setting the Type to be Summary and selecting the AlertQA07_VeryPoor as the field to total.

           

          Setup these sets for each option in each field, which can take a while to setup, and place the Total# fields on the layout and it will total your found set.

           

          You can also use these in sub-summary parts to produce grouped reports.

           

          I hope this points you in the right direction. 

          • 2. Re: Totalling value list responses in a Calculation field
            raybaudi
              

            Hi Orlando

             

            I can't resist... ;)

             

            If ( QA07 = "Very Poor" ; 1 ; "" )

             

            or, simpler:

             

            QA07 = "Very Poor"

            • 3. Re: Totalling value list responses in a Calculation field
              Orlando
                

              Quite right Daniele, habit of mine to do it that way.

               

              I did actually wonder if you would comment as I posted :)

              • 4. Re: Totalling value list responses in a Calculation field
                raybaudi
                  

                Orlando

                 

                "I did actually wonder if you would comment as I posted"

                 

                No, this is only the demostration that I read ALL the posts and this is because I know that in this way I will learn more and more.

                 

                And sure there is a lot to learn from you.

                • 5. Re: Totalling value list responses in a Calculation field
                  trevorlawson
                    

                  Thanks Orlando and Daniele - your help has been brilliant.  I combined both your solutions, using Daniele's If ( QA07 = "Very poor" ; 1 ; "" ) in one new field and then totalling the 1s in a separate summary field.  Trying to get to grips with the syntax here, I guess 1 is a constant. But what does the "" do?

                   

                  Big thanks.  Trevor 

                  • 6. Re: Totalling value list responses in a Calculation field
                    Orlando
                      

                    Glad to help Trevor

                     

                    The 1 is as you say a constant that will be added up in our summary, the "" is effectively Null, which meant the field will be empty if the test in you If statement does not match. 

                    • 7. Re: Totalling value list responses in a Calculation field
                      trevorlawson
                        

                      Hi Orlando and Daniele.  I wonder if you can help me further?  I also have a question where the answers are held in a Checkbox set.  So if question QC02 asks "Who do you ask for Calculation syntax advice?", there are check boxes for:

                       

                      Orlando

                      Daniele

                      Trevor

                       

                      Most people check both Orlando and Daniele but very few people check Trevor, too.  How can I sum how many people check each box?  The mechanism given for the drop down menu doesn't work and I'm not sure what to do next.

                       

                      I've tried:

                       

                      If ( QC02 = "Orlando" ; 1 ; "" )

                       

                      but to no avail. 

                       

                      Thanks a million for any advice you can offer.

                       

                      Trevor 

                      • 8. Re: Totalling value list responses in a Calculation field
                        raybaudi
                          

                        Hi Trevor

                         

                        you had to give credits only to Orlando.

                         

                        The calculation that you are using is their; mine was:

                         

                        QA07 = "Very Poor"

                         

                        that is a Boolean calculation, meaning that the result will be 1 IF ( and ONLY IF ) QA07 is equal to "Very Poor".

                         

                        Now to the new problem...

                         

                        Checking Orlando and Daniele is the same that writing inside that field:
                        Orlando¶Daniele

                         

                        So QC02 will be = "Orlando" only one time on 3.

                         

                        The new ( 3 ) calculation will than be:

                         

                        If ( Lenght ( FilterValues ( QC02 ; "Orlando" ) ) ; 1 ) for count Orlando values

                         

                        If ( Lenght ( FilterValues ( QC02 ; "Daniele" ) ) ; 1 ) for count Daniele values

                         

                        If ( Lenght ( FilterValues ( QC02 ; "Trevor" ) ) ; 1 ) for count Trevor values

                         

                        Note that even those 3 calculation may be made as Booleans; for example the first can be :

                         

                        Lenght ( FilterValues ( QC02 ; "Orlando" ) ) > 0

                        • 9. Re: Totalling value list responses in a Calculation field
                          trevorlawson
                            

                          Thanks Daniele - you're brilliant.  That works perfectly.  

                           

                          Incidentally, I've also tried your first solution and that worked too, but the Filemaker forum only allows me to choose one solution.

                           

                          And don't worry - your English is far better than my Italian. Your quick help is very much appreciated.

                           

                          Very best wishes,

                           

                          Trevor 

                          • 10. Re: Totalling value list responses in a Calculation field
                            trevorlawson
                              

                            Hi Daniele and Orlando,

                             

                            I hope you had a great Christmas.  I wonder if you could provide another piece of advice?

                             

                            If I wanted to total how many people checked both "Orlando" AND "Daniele", I would have thought that the syntax should look like this:

                             

                            If ( Length ( FilterValues ( QC02 ; "Orlando" and "Daniele" ) ) ; 1 ) 

                             

                            or maybe

                             

                            If ( Length ( FilterValues ( QC02 ; "Orlando" + "Daniele" ) ) ; 1 )  

                             

                            But this doesn't work.  Where am I going wrong here? 

                             

                            And if you don't get this message until 2009 - happy new year!

                             

                            Best wishes,

                             

                            Trevor 

                            • 11. Re: Totalling value list responses in a Calculation field
                              raybaudi
                                

                              Hi Trevor

                               

                              your calc was near the solution...

                               

                              but to filter values you have to give it values !

                               

                              So:

                               

                              If ( Length ( FilterValues ( QC02 ; "Orlando¶Daniele" ) ) ; 1 )