1 2 Previous Next 20 Replies Latest reply on Feb 16, 2017 4:44 PM by BruceRobertson

    Count of calculated fields with specific results?

    ezeitgeist

      What is the best route to get the sum of the number of calculated fields with a specific result?

       

      I have 200 calculation fields that calculate (based off of a different table record) either a "+", an "O", or a letter.

       

      I want to calculate the number of "+" results, the number of "O" results, and the number of neither "+" nor "O" results. Can I use a count function on a calculated field? Can I use an ExecuteSQL?

        • 1. Re: Count of calculated fields with specific results?
          coherentkris

          ExecuteSQL ( SELECT COUNT(the_field) FROM the_table WHERE the_field=? OR the_field=? ; "" ; "" ; "O" ; "+")

          ExecuteSQL ( SELECT COUNT(the_field) FROM the_table WHERE the_field=?; "" ; "" ; "O")

          ExecuteSQL ( SELECT COUNT(the_field) FROM the_table WHERE the_field=?; "" ; "" ; "+")

          ExecuteSQL ( SELECT COUNT(the_field) FROM the_table; "" ; "" )

          • 2. Re: Count of calculated fields with specific results?
            ezeitgeist

            So if I have 200 fields in this record, and I want to count the occurrence of "+" in each field (ie. 120 "+"s, "50 "O"s, and 30 "Others"), so I ... get confused. Do I have to create an ExecuteSQL for EACH field and then do a sum somehow?

            • 3. Re: Count of calculated fields with specific results?
              coherentkris

              if you have 200 fields in a table that you want to count and sum you've probably got more problems than how to count and sum.

              If the 200 fields are in fact the best data design (it is possible) then I would write a script to do the calculations across all the necessary fields.

               

              Using ExecuteSQL is not the only choice but it is the first one that came to mind. I would go a little deeper into the the issue and want to know exactly what those 200 calculated fields are for to see if there was a simpler way to accomplish the goal of simplified count/sum reporting.

              • 4. Re: Count of calculated fields with specific results?
                ezeitgeist

                Unfortunately, the 200 fields is simplest route. I am creating a testing project and the fields represent the test answers (within a Results table). There are 200 test question fields, then 200 calculations that grade each question based on the assigned Answer Sheet table. Since the Test types will change, having a set number of question fields, I can have the Test type change (associated Test Answer Sheet table record) and yet go through the same Results table for grading.

                 

                I thought of having each individual question a record in a table, but then that would get to an insane number of records and entering the data of answers for each test would became insanely time-consuming.

                 

                Let me know if that makes sense. The ExecuteSQL was my thought first too, just don't know how to apply it to fields within the same record. The script across necessary fields was my end thought, I just don't know how to approach that (no experience in that realm).

                • 5. Re: Count of calculated fields with specific results?
                  arjen.evertse

                  If you really want to go with keeping all fields in one record I guess your best chances are to add calculation fields:

                   

                  ValueCount ( FilterValues ( List ( AnswerField1 ; AnswerField2; AnswerField3; [197 more...] ) ; "+" ) )

                   

                  This will return the total amount of + answers, you can do the same for O. The last one depends on the answers that could possible filled in or you could go with calculation total amount of answer fields minus the two results you already have. This will however also count empty fields.

                   

                  It will work, but it is very brittle design.

                  2 of 2 people found this helpful
                  • 6. Re: Count of calculated fields with specific results?
                    philmodjunk

                    "I thought of having each individual question a record in a table, but then that would get to an insane number of records and entering the data of answers for each test would became insanely time-consuming."

                     

                    A table ccan store millions of records so what number would you consider "insane"?

                    and entering answers into multiple records, 1 answer to a record need not take any more time than doing the same with multiple fields in the same record.  Separating your questions and their associated answers is vastly more flexible.

                    • 7. Re: Count of calculated fields with specific results?
                      ezeitgeist

                      The issue with 1 question = 1 record means I would also have to associate the question with the correct specific test, the specific test type, and create a way to keep track of the proper order of the questions as well. It gets exponentially more complicated that way. If the questions are all in the same test record, they have one test name, one test ID, and are entered in the order needed already.

                      • 8. Re: Count of calculated fields with specific results?
                        philmodjunk

                        What you describe is exactly how you should set this up. In the long run, it is less complicated.

                         

                        Yes, your data model will be more complicated. Yes your interface design will take more thought to produce an effective interface. But you also gain data that is vastly easier to work with for analysis and reporting tasks. Problems like the one you have here pretty much disappear.

                         

                        I would not call that "exponentially more complicated".

                        • 9. Re: Count of calculated fields with specific results?
                          BruceRobertson

                          Let me rephrase your reply.

                           

                          "By ignoring standard data practices, I have created a problem that I don't know how to solve.

                          So I choose to ignore your advice and instead, ask you to do the work. Please spend large chunks of time and ingenuity to fix a problem that I don't understand and which shouldn't even exist."

                          • 10. Re: Count of calculated fields with specific results?
                            user19752

                            The values are only one character, so it can be

                            PatternCount ( AnswerField1 & AnswerField2 & AnswerField3 & [197 more...] ) ; "+" )

                            • 11. Re: Count of calculated fields with specific results?
                              arjen.evertse

                              True about the PatternCount.

                               

                              But as many pointed out, although it works for the current question the data structure is not following best practice and surely not future proof. Fixing this today will most likely result in another issue another day were there might be no solution.

                              • 12. Re: Count of calculated fields with specific results?
                                IT_User

                                ezeitgeist wrote:

                                 

                                The issue with 1 question = 1 record means I would also have to associate the question with the correct specific test, the specific test type, and create a way to keep track of the proper order of the questions as well. It gets exponentially more complicated that way. If the questions are all in the same test record, they have one test name, one test ID, and are entered in the order needed already.

                                I've done that. A little complex, but it works.

                                 

                                ezeitgeist wrote:

                                 

                                entering the data of answers for each test would became insanely time-consuming.

                                 

                                Does this mean the answer choices or the actual answers? If actual answers, what is entering the data?

                                • 13. Re: Count of calculated fields with specific results?
                                  LSNOVER

                                  Can't you just make a calculated field for each row record. 

                                  IF(Field1=X,1,0)+If(Field2=X,1,0), ....   Then sum this field(s) using a calculation.   You could do one calc for X, one for O and one for blank if you want to count them separately. 

                                  • 14. Re: Count of calculated fields with specific results?
                                    siplus

                                    Create a calc that puts together all your fields using &:

                                     

                                    allFields = Field1 & Field2 & Field3.... & Fieldn.

                                     

                                    then

                                     

                                    Length ( Filter ( allFields; "+" ) ) will tell you how many +

                                     

                                    Length ( Filter ( allFields; "O" ) )  will tell you how many "O"

                                     

                                    Length ( Substitute ( allFields; ["+"; ""]; ["O"; ""] ) ) will tell you how many letters you have.

                                     

                                    Everything said by other posters about the lack of flexibility in your implementation still holds.

                                    1 2 Previous Next