6 Replies Latest reply on Jul 10, 2015 12:54 PM by philmodjunk

    Charting percentages in delimited view

    Atcha

      Title

      Charting percentages in delimited view

      Post

      Hi,

      I am completely stuck trying to get a percentage display in a pie chart and don't see what I am doing wrong. perhaps someone can set me straight. I have a field a in Table A linked via join table X to table B and I would like to display in percentages how many associated records a specific field a has in table B as compared to the total number of associated records in table B for all values of field a.

       

      Table A already has a calculated field (freq) that shows how many records in Table B are associated with a specific value in Table A and the join Table contains a summary field for the number of records in that table (this corresponds to the total number of associations between tables A and B). I set up a pie chart in the form based on Table A, with the DataSource based on the current record, the Category label of the chart based on the freq field and the Slice Data based on the summary field in the join table giving me the total number of records and hence associations. This does not work: it gives me 100% whatever I do. The total number of records is clearly limited to the one record in view, but I cannot switch to an undelimited view because then I am presented with all values for field a. How do I set this up so I see a pie chart showing a precentage of a whole?

        • 1. Re: Charting percentages in delimited view
          philmodjunk

          "delimited data" means that all the data to be charted exists inside a single field of the current record separated by return characters. Do you have such a field? You'd need one for your Y series and one for your X-series in order to provide the labels needed. Both the List function and ExecuteSQL can be used to generate such delimited data series.

          The "alphabet soup" of your description also makes it difficult to follow. Actual table and field names can be helpful.

          • 2. Re: Charting percentages in delimited view
            Atcha

            "Alphabet soup" hè, and I was trying to avoid confusion (-:. Ok, here goes. For the many-to-many relationship see the enclosed screenshot.

            As to the delimited data, no I do not have that. I took "Current record" to refer to data based on a current record rather than a whole set of records.

            I would like to show in the form of a pie chart how often individual animals appear in devices compared to how often all animals appear in devices (in percentages). So if a lion appears in 500 devices and all animals appear in some 5000 devices altogether, this would be 10%. The pie would then consist of a single 10% slice and another one of 90%.

             

            Ideally, I would (also) like to get an overview of all the relative frequencies of animals and I actually use a stacked bar for that, but since so many species are involved this bar becomes rather unwieldy. Can't I just use a small window with scroll-bars in a form, so I don't have to adjust its size all the time as new species are added and users can just scroll down?

             

            • 3. Re: Charting percentages in delimited view
              philmodjunk

              I took "Current record" to refer to data based on a current record rather than a whole set of records.

              That is correct, but if you are going to plot more than one value in your chart, a field in that single record has to have the needed data in it in order for you to generate a meaningful chart. Since you describe two wedges in your example (10% and 90%, you need two values to chart in order to get the needed pie chart. This can be done with a calculation field that separates the two values with returns or you can set up a calculation for your Wedge data that produces the same list of values.

              So if a lion appears in 500 devices and all animals appear in some 5000 devices altogether, this would be 10%

              Are you able to calculate the 10%? If so, you only need subtract that value from 1 (100%) in order to get the second delimited value for your pie chart.

              and does 5000 represent the total number of records in devAnim or some other count?

              From a layout based on Animals, count ( DevAnim::animalID ) would appear to return the 500 from your example. If TotalRec is a "count of" summary field, it should return the same result as count here.

              ExecuteSQL ( "SELECT Count ( AnimalID ) FROM DevAnim" ; "" ; "" )

              would seem to return the correct count of all records in DevAnim. Thus:

              Let ( pct = Count ( DevAnim::animalID ) / ExecuteSQL ( "SELECT count ( AnimalID ) FROM DevAnim" ; "" ; "" ) ;
                      List ( pct ; 1 - pct )
                    ) // Let

              would appear to work to produce your wedge data.

              List ( Animals::Animal ; "All Others" )

              would appear to work to provide labels for your two wedges.

              This solution requires FileMaker 12 or newer. A different approach would be needed for older versions.

              • 4. Re: Charting percentages in delimited view
                Atcha

                I'll be darned, that works like a charm. SQL here I come! Thanks a lot (once again).

                • 5. Re: Charting percentages in delimited view
                  Atcha

                  PS. Is it correct that ExecuteSQL scripts are not supported in web publishing, i.e. that when I want to use a database in which such statements have been used they will not work on the web?

                  • 6. Re: Charting percentages in delimited view
                    philmodjunk

                    SQL was just a quick way to get the total number of records in the table. There are nonSQL ways to get the same value.

                    I also realized after the fact that I was having the calculations do more than is truly needed. You just need the count for each wedge. You should not need to compute percentages.

                    Let ( [ Wedge1 = Count ( DevAnim::animalID ) ;
                              Wedge2 = ExecuteSQL ( "SELECT count ( AnimalID ) FROM DevAnim" ; "" ; "" ) - Wedge1
                            ] ;
                            List ( Wedg1 ; Wedge2 )
                           )

                    should also produce the same pie chart.