4 Replies Latest reply on May 31, 2010 7:06 AM by otto_m

    Please help me create a pie chart from 5 summary fields

    otto_m

      Title

      Please help me create a pie chart from 5 summary fields

      Post

      Hi all,

       

      I just freshly installed FileMaker 11 on MacOS 10.6.3. I have been using FM for a while, but I am not an expert in database design. Here's my story:

       

      I have used Numbers to collect information about the skill levels of applicants to our school, but Numbers has become painfully slow with 1500+ data sets and charts for 25 different degree programmes. When I learned that FM 11 does charts, I decided to convert my data to a FM database.

       

      I imported a csv file into a new database called "placement_2010". Among others, FileMaker created the following fields:

       

      Name (Text Indexed)

      Gender (Text Indexed)

      Date of Birth (Date)

      Programme (Text Indexed)

      Score (Number)

       

      To count the number of applicants at various skill levels, I followed your previous advice and created those fields using the "Score" field as a starting point:

       

      Level1 = Score ≤ 21

      Level2 = (Score ≥ 22) - (Score ≥ 43)

      Level3 = (Score ≥ 43) - (Score ≥ 85)

      Level4 = (Score ≥ 85) - (Score ≥ 153)

      Level5 = Score ≥ 153

       

      To break down the skill levels by "Programme", I created the following summary fields:

       

      SumLevel1 = Total of Level1 (running with restart), when sorted by placement_2010::Programme

      SumLevel2 = Total of Level2 (running with restart), when sorted by placement_2010::Programme

      SumLevel3 = Total of Level3 (running with restart), when sorted by placement_2010::Programme

      SumLevel4 = Total of Level4 (running with restart), when sorted by placement_2010::Programme

      SumLevel5 = Total of Level5 (running with restart), when sorted by placement_2010::Programme

       

      This works well in a layout consisting of two sub-summary fields with "Programme" as the break field in the lead and the SumLevel fields in the trailing sub-summary. Here's an example:

       

      ================================

      Programme = BBE

      total number of applicants: 145

      average score: 100.17

      ================================

      SumLevel1 = 2

      SumLevel2 = 9

      SumLevel3 = 36

      SumLevel4 = 89

      SumLevel5 = 9

      ================================

       

      However, I have found no way to visualize the above values in a pie chart in the same sub-summary field. Whatever I do, FM shows nothing or breaks the pie into 145 wedges. This psychedelic view does not quite meet my requirements.

       

      I also consulted FileMaker Help and the Introduction to Charting to no avail. Can you help me create a usable pie chart from that data?

       

      Thanks,

       

      Otto

        • 1. Re: Please help me create a pie chart from 5 summary fields
          comment_1

           


          otto_m wrote:

          I followed your previous advice and created those fields using the "Score" field as a starting point:

           

          Level1 = Score ≤ 21

          Level2 = (Score ≥ 22) - (Score ≥ 43)

          Level3 = (Score ≥ 43) - (Score ≥ 85)

          Level4 = (Score ≥ 85) - (Score ≥ 153)

          Level5 = Score ≥ 153


           

          I don't know who gave you this advice; it sure wouldn't have been mine. I suggest you use ONE calculation field for the level - it could be something like cLevel =

           

           

          Sum ( 
          1 ;
          Score ≥ 22 ;
          Score ≥ 43 ;
          Score ≥ 85 ;
          Score ≥ 153
          )

           Then summarize your records by Programme and by cLevel. I believe the chart will also work itself out once you straighten out this mess.

           


          • 2. Re: Please help me create a pie chart from 5 summary fields
            otto_m

            Thank you for your comment.

             

            I have created the Sum field as you suggested, and have three additional questions:

             

             

            • When I place the Sum field in either sub-summary part of my layout, it returns the value "4". What is the field meant to do?

             

            • If I understood you correctly, I need to create a sub-summary field that is defined by two fields. How do I summarize records by Programme and cLevel at the same time?

             

             

             

            • What data values do I use to define the pie chart?

             

            • 3. Re: Please help me create a pie chart from 5 summary fields
              comment_1

               


              otto_m wrote:

              If I understood you correctly, I need to create a sub-summary field that is defined by two fields.


               

              Not really. In addition to the cLevel calculation field, you need two summary fields:

              sAvgScore: Average of Score;

              sCount: Count of ApplicantID (or any other field that cannot be empty).

               

              To get the report described above, you need the following layout parts and fields:

               

              Header

              Subsummary by Programme (Leading):

              • sCount

              * sAvgScore

              Subsummary by cLevel (Leading):

              • cLevel

              • sCount

              Footer

               

              Note there is no Body part.

               

              Place the chart in the Subsummary by Programme part. For the chart, use cLevel as Label data and sCount as Value data, and check the "Show data points for groups…" option. Sort your records by Programme and by cLevel and that should be it.


              • 4. Re: Please help me create a pie chart from 5 summary fields
                otto_m

                Thanks a million for your swift help:smileyhappy: