8 Replies Latest reply on Oct 20, 2012 9:23 AM by mdiehr

    Histogram function?

    benlevi

      My client has a series of surveys, each with a number of questions, each response being a number on a Likert scale (1-5, or 1-9, etc.)

       

      I've been tasked with giving them a 'histogram' of each question, where for a found set, it would tell how many chose 1, 2, 3, etc.

       

      I can do it with each question being on a separate layout by sub-summary based on the response and using a Summary field to Count the responses. This effectively produces a report like this:

      1 10

      2 3

      3 1

      4 9

      5 5

       

      Where 10,3,1,9,5 are the # of respondents who chose 1,2,3,4,5 respectively. I can then produce a bar graph which is effectively a histogram.

       

      The problem is that each question needs its own layout because it needs to sort the found set by that question. My client would like to see all the results for a particular survey on a single layout.

       

      Is there a way to create a custom function that has as its input a field, and a 'Likert number' like 5 (or I can create a HistogramQ1 Repeating Field of 5 to put the answers into), and the function would go through the found set, count the # of repondents who chose 1, 2, etc. and place that count into the HistogramQ1 repeating field, so with the above example, the results would be:

      HistogramQ1 = (10,3,1,9,5)

       

      I tried looking in Brian Dunning's CF page, but didn't find anything (note I didn't go through the whole list in detail though).

       

      Thanks!

        • 1. Re: Histogram function?
          mdiehr

          I'm not sure why you say "each question being on a separate layout" -- if true, this sounds as if your data table is not normalized.  If so, you probably do have to use a separate layout for each question, unless you do the right thing which is to go back in an properly normalize your data.

           

          Normalization is a fairly difficult concept to understand, but here's a way to tell if your data is normalized:

          • If a table has fields named like this: Question01, Question02, Question03, or  email1, email2... or you are using repeating fields, then your data is probalby not normalized.

              

          The trick for normalization is to avoid building the number of the item into the field name itself, instead you need to have that as a separate field.

          • 2. Re: Histogram function?
            benlevi

            Mdiehr,

            The reason I need to have each question's histogram on a separate layout is that each question must have its records sorted by that question, so all the 1's, 2's, 3's, etc. are grouped together. Then the chart can show the Count of each of the 1's, 2's, 3's, etc. which gives me the histogram. Since each layout needs its own sort for a particular question, I need many layouts, one per question.

            • 3. Re: Histogram function?
              mdiehr

              Can you be more specific as to how your tables are designed?    What are the field names and how is the data stored (by row, by column, etc?)

              • 4. Re: Histogram function?
                benlevi

                Each response is its own Number field, e.g.

                rate_1_1

                rate_1_2

                rate_1_3

                 

                and for each i have a Summary field:

                rate_1_1 Count = Count of rate_1_1

                etc.

                 

                Not sure what you mean by 'stored by row or column'...

                • 5. Re: Histogram function?
                  mdiehr

                  If I were approaching this problem from scratch, here's what I would do:

                   

                  Make a table called "Questions" which has an ID an question text, like this:

                   

                  • ID QuestionText
                  • 1 How did you like the food?
                  • 2 Was your waiter friendly?
                  • 3 ... etc...

                   

                  Then I'd have a Data table that recorded, for each person surveyed, the question ID and their answer, like this:

                  • PersonID QuestionID Response
                  • 1001 1 4
                  • 1001 2 5
                  • 1001 3 6
                  • ...etc...

                   

                  Set up a relationship between the Questions and Data tables (Questions::ID = Data::QuestionID)

                   

                  Wit this properly normalized relationship, you can then easily build a single layout that does histograms for all your data, just sort by QuestionID, PersonID, Response

                   

                  I'd also probably set up an "Answers" table which is just a simple reference table that maps the Likert numbers to descriptions, e.g.

                  • AnswerNumber AnswerText
                  • 1 Very Litte
                  • 2 Not very much
                  • 3 Medium
                  • 4 A lot
                  • 5 Very much

                  (or whatever values you are using...)         

                  • 6. Re: Histogram function?
                    benlevi

                    That's a good solution. Thank you. Perhaps I can create a table that takes the existing questionnaire data and generates your Data table above.

                    • 7. Re: Histogram function?
                      mdiehr

                      Thanks for exaplaining.   As I suspected, you've got a fully un-normalized data table.   This is a perfect example that illustrates the answer to the question "Why should I bother with normalization?"

                       

                      If it's not too late, I strongly suggest you learn about normalization and redesign your tables & relationships into 3NF, BNF or 4NF or something approaching that.

                       

                      If it's too late, then I think you'll have to just deal with it some other way, such as

                      • making dozens of layouts, one for each question (the pain of this will be a good life lesson) :-)
                      • reorganize your data back into a normalized format.  You can probably do this with some clever use of scripting and the go to next field function - basically you'd copy your data out of the existing format and into a format more like I've described in my other post.
                      • 8. Re: Histogram function?
                        mdiehr

                        Yes, I think that's a good approach.  I would do this one of two ways:

                        1. Put all the fields you need to pull data from on a layout, and make sure they are in proper order (rate_1_1, rate_1_2).   Using a looping script, use the "Go to Next Field"  command to move the focus from field to field, and "Copy" and "Paste" to copy & paste data to your other table.   This technique works even if your field names aren't regular.
                        2. If your fields are nicely named (rate_1_1, rate_1_2...) then you might try a script that builds the field name using variables, and then used the Evaluate() function to get the field data based on it's name.   E.g. SetVariable $fieldName = "rate_" + $i + "_" + $j)  where $i and $j are variables that you increment in a loop inside a loop.