10 Replies Latest reply on Apr 25, 2013 7:10 AM by philmodjunk

    Creating Charts from Multiple Fields Using Summary Count Totals

    AlanJames

      Title

      Creating Charts from Multiple Fields Using Summary Count Totals

      Post

           I am trying to create a chart that displays data from several fields over a large found set, but displays only one bar per field it is displaying.

           Easiest done by simple example:

           - I have 20 people all of whom can own a red, blue or a green hat. They can own more than one hat. 

      13 people own a red hat

      a different 12 people own a blue hat

      and 5 people own green hat

           In my database I can easily create a Count Summary field that contains the numbers 13, 12 and 5 when I find the full set of 20 people, or even the appropriate set when I find fewer records.

           My problem is, how do I get Filemaker 12 to display this Count Summary data in a bar chart.

            

           Apologies if this has been asked before but I couldn't find it on search.

        • 1. Re: Creating Charts from Multiple Fields Using Summary Count Totals
          philmodjunk

               One sentence throws up a possible red flag here:

               

                    They can own more than one hat.

               Does that mean the same person could own both a red and a blue "hat"? If so, how do you record the ownership of multiple kinds of hats by the same person?

               If we ignore that issue for now, you can use the same approach as your summary report with sub summary parts, but each bar in your chart will chart the same data you might put in the sub summary layout part of a summary report.

               Select the summary field for your Y-Axis values. and then, in the Data Source section of the Chart Setup, select "current found set" and "summarized groups of records".

               There is one key limitation that I recently discovered though: This works as long as you specify a single field in your sort order so that you group records by that single field. This can require some fussing around with special calculation fields that combine data from multiple fields in order to get a single "sort key" field for correctly sorting your records.

          • 2. Re: Creating Charts from Multiple Fields Using Summary Count Totals
            AlanJames

                 I record the ownership of multiple hats by having a separate field for each colour of hat.

                 I tried what you suggest but I still get many bars appearing on my chart.

                  

            • 3. Re: Creating Charts from Multiple Fields Using Summary Count Totals
              philmodjunk

                   Did you sort your records to group them?

                   

                        I record the ownership of multiple hats by having a separate field for each colour of hat.

                   I was afraid that might be what you were doing. That data model won't work for your chart. A related table where you have one record for each type of hat for each customer will be a much more flexible way to go in addition to making the chart work for you.

              • 4. Re: Creating Charts from Multiple Fields Using Summary Count Totals
                AlanJames
                     

                I was afraid that might be what you were doing. That data model won't work for your chart. A related table where you have one record for each type of hat for each customer will be a much more flexible way to go in addition to making the chart work for you.

                Won't that create a Many-to-many relationship?  Many customers, and many hats.

                     Perhaps the real data model I am trying to solve might be a better example.

                     I have 5000+ replies to a customer survey on rock climbing preferences. Many questions have the possibility for multiple selections by each person. For example, "What type of climbing do you do?" - Sport, Bouldering, Alpine, etc. Readers can select one or more of these options.

                     The raw survey data I have is in a tab separated file, with the results from questions that offer multiple options separated by commas (ie.TAB "sport,boulder,alpine" TAB). I can preformat this data anyway I want so I could import the multiple answers into separate fields, or alternatively, I could try and anaylse it as it is (although I have no idea how to do that).

                      

                     I want to know how many of my 5000+ in the survey  go Sport Climbing, and how many Boulder, etc. and illustrate it in a graph that responds to the current data set. I have other questions that analyse purchase habits, so my ultimate aim is to be able to analyse, for example, what percentage of Black Diamond customers go sport climbing, and what percentage go bouldering, etc.

                     I really apprecaite any help that anyone can give on this.

                      
                • 5. Re: Creating Charts from Multiple Fields Using Summary Count Totals
                  philmodjunk

                       Yes, it's a many to many relationship and is typical of any invoicing system where records in Orders have a many to many relationship with Products. A join table, lineItems (or InvoiceData in the FileMaker 12 starter solution) serves as the link between the two and you can place a chart of this type on a layout based on the join table.

                       The same should apply to your survey as well.

                       You might find this thread on surveys and questionaires useful: Need aid on generating a report from a survey layout.

                  • 6. Re: Creating Charts from Multiple Fields Using Summary Count Totals
                    AlanJames

                         Thanks. That is really useful and I think I have cracked the basic structure.

                         I assume the Responses table has a number of fields equal to the total number of options to the question with the most option answers? ie. for my type of climbing question, tthere are 12 types of climbing to choose from so I would need 12 fields in the responses table.

                         One new problem that I have is that I can now create my dynamic graph based on the found set, but how do I get a specific set of labels for the x-axis under each column in this bar chart? In the example below, where the number 1929 is above 'Different Types of Climbing'

                          

                    • 7. Re: Creating Charts from Multiple Fields Using Summary Count Totals
                      philmodjunk

                           I assume the Responses table has a number of fields equal to the total number of options to the question with the most option answers?

                           No. I would use one field with twelve possible values that can be entered into a single field of a single record, one record for each question for each respondent.

                           


                                but how do I get a specific set of labels for the x-axis

                           Do you have this data in a field of either the layout's table or a related table?

                      • 8. Re: Creating Charts from Multiple Fields Using Summary Count Totals
                        AlanJames

                              

                             

                                  I assume the Responses table has a number of fields equal to the total number of options to the question with the most option answers?

                             

                                  No. I would use one field with twelve possible values that can be entered into a single field of a single record, one record for each question for each respondent.

                             Hmm. But this is the check box question where one respondent can have more than one area of interest. So would you advise that each part answer to the question has its own record. ie.In reply to what type of climbing do you do,  Jack goes sport climbing AND alpine climbing. That would create two records for this question - Jack > sport and then another Jack > alpine where sport and alpine were just values in the same field in different records?

                             

                                  Do you have this data in a field of either the layout's table or a related table?

                             I could easily do that, or I could create a value list. Perhaps the best place might be in the Question Table where possible answers are listed.

                              

                        • 9. Re: Creating Charts from Multiple Fields Using Summary Count Totals
                          AlanJames

                               Ignore above. I have now solved the problem by doing as you suggest - a single field in the responses table that holds every single reply including multiple replies from the same respondent to the same question. It means that there are millions of records in the responses table but it also enabled me to create a single data series for each question which makes the charting a piece of cake (or piece of pie might be more appropriate).

                               Thanks again for your help.

                          • 10. Re: Creating Charts from Multiple Fields Using Summary Count Totals
                            philmodjunk

                                 Often a Checkbox field that stores multiple responses seems the best way to go when your start your design, but then you find that you need to process/analyze the results in a way that enumerates the individual values selected. In most cases, this is much more easily accomplished using separate records in a related table. This change, however doesn't need to be visible to the user. They can still be Clicking checkboxes to select values if you design your layout in one of several ways that still presents the user with a list of checkboxes that are actually checkboxes that enter single values in to the same field of separate records.