9 Replies Latest reply on Apr 10, 2015 6:43 PM by philmodjunk

    Pie Chart With Summary Fields?

    general@gunsmoke.com

      Title

      Pie Chart With Summary Fields?

      Post

      I'm trying to create a pie chart that shows the number of records in the found set as a fraction of the total number of records in the table.

      For example, if there are 10 records in the table, and the last "find" created a set with three records, I'd like to see a pie with a 30% piece and a 70% piece for the remainder. Sounds simple, but being new to charting the answer is eluding me at this point.

        • 1. Re: Pie Chart With Summary Fields?
          philmodjunk

          Specify the summary field as the field for your wedge size (y - series). But make this a total summary field rather than "fraction of total".

          Use the "summary data, found set, when sorted" data source option and be sure to sort your records to group them correctly.

          • 2. Re: Pie Chart With Summary Fields?
            general@gunsmoke.com

            Thanks for the quick reply!

            By wedge size, do you mean "Slice Data" under the Chart list in the Chart Setup dialog box?

            The wedge size is a calculation field that simply calls the Get ( FoundCount ) function. I use Get ( TotalRecordCount ) to determine the number of records in the table.

            I'm not familiar enough with FMP to know the difference between a total summary field  and fraction of total. Is that an alternate way to determine the number of found records and the number of records in the table?

            What I'm seeing so far is that FMP is attempting to create a slice for each row in the found set vs. using the calculation results in the previous paragraph.

            FWIW, I'm using V13.01 on a Mac, and don't see the options you've described.

             

            Thanks, I really appreciate the help!

            • 3. Re: Pie Chart With Summary Fields?
              philmodjunk

              wedge size = Slice Data

              In the thread's total you used the term "summary data" and also referred to "fraction of total". So I thought you were using a fraction of total summary field. Get ( FoundCount ) won't work here.

              Go into Manage | Database | Fields and add a summary field that computes the "count of" any other field in the table that is never empty. Specify this summary field as the field to use for your slice data.

              What I'm seeing so far is that FMP is attempting to create a slice for each row in the found set

              This is why you need to sort your data to group them by whatever field defines the groups that make up each wedge in your pie chart. The data source options are found at the bottom of chart set up. I didn't identify them precisely as I was going by memory, but figured that they were close enough for you to figure it out. See the attached image.

              • 4. Re: Pie Chart With Summary Fields?
                general@gunsmoke.com

                Sorry to confuse you. I'm still learning the jargon, and have trouble referring to things by their proper names.

                The task at hand is to determine how many days two people were in the same location.The database contains just one table. Fields are Date, Location, and Name, plus a calculation field called DateLocation that is a concatenation of the Date and Location fields. The table has a record for each person for each day, so two records for each day. That's probably a schema problem right there, but again, I'm pretty new to this. To find the dates when both people were in the same location, I execute a find with the "!" operator in the DateLocation field with Matching Records set to Include. As such, there's nothing to sort on in the result set because it contains records only for the days that the two people were colocated.


                Something tells me that my approach needs to be altered if I'm going to get a pie chart out of FMP, but at this point I'm at a loss as to what to try next. Any ideas?

                • 5. Re: Pie Chart With Summary Fields?
                  philmodjunk

                  What does each wedge in your chart represent?

                  After reading your last post, that is not clear.

                  The number of people at the same location at the same time?

                  Sort your records by the field that concatenates location and date.

                  The number of people at the same location regardless of time?

                  Sort your records by location.

                  The purpose of sorting your records is to group your records in to groups of records with a common value. Each such group then becomes a wedge in pie chart.

                  BTW, a table where you have one record for each person's location and date when they were at that location seems exactly correct for what you want to do here.

                   

                  • 6. Re: Pie Chart With Summary Fields?
                    general@gunsmoke.com

                    A wedge represents the number of days the two people were in the same location. The remaining portion of the pie represents the number of days they were apart. It doesn't matter what the location is (in this case it can have three values). As long as the two people were colocated on the same date, they are members of the result set.

                    For example, if the two people were in any of three different locations on the same day 20 times over the course of a 100 day period, one wedge would be 20 units wide, the other 80.

                    Does that help?

                    A person can be in only one location on any given date. When both people are in the same location on the same date, the duplicate operator ("!")  will Include both records in the result set for that particular date/location combo. In other words, the two records will have matching DateLoction fields, but different Name fields. Because the duplicate operator will match two records (one for each person on any particular date) the number of records in the result set will be twice the number of days the two people were actually together.

                    Thanks for your patience. Maybe I should just plug the numbers into Excel?

                    • 7. Re: Pie Chart With Summary Fields?
                      philmodjunk

                      Yes, but you can't perform a find for duplicates and get the pie chart that you want. Your pie chart has to count all of the days and group them into two groups, one for dates when they were together at same place and time and one for each date that they were not together at same place and time.

                      It does seem like you need a change in your data model. While you would still keep this original table, I suggest creating an "schedule" table with one record for each date to use as the table on which to base your layout and the chart that you'll place on it. You can then add a calculation field to this table, cTogetherFlag defined as:

                      ExecuteSQL ("
                      SELECT Count ( Distinct \"Location\" ) FROM \"YourOriginalTableNameHere"
                      WHERE \"DateField\" = ? " ;
                      "" ; "" ; DateField )

                      Note that this last DateField is the name of the Date field your new table while \"DateField\" is the name of the date field in your original table.

                      This field will return a 1 when both records for that date have the same location and 2 when the two records have a different location.

                      If you sort your records by this field, you will get two groups of records, one where the value is 2 and one where the value is 1. A "count of" summary field can then be specified for Slice data and you'll get your two wedges, one for when they are together and one for when they are apart.

                       

                      • 8. Re: Pie Chart With Summary Fields?
                        general@gunsmoke.com

                        Yes, I see now that the pie chart works only on a per record group basis.

                        OTOH, thanks for the reminder that it is possible to use SQL in calculations. Very handy, and removes a great many limitations from FMP. I was able to get it work, and got a very useful result.

                        It occurs to me, though, that anytime one uses a pie chart, it is required to sort the data. As such, it isn't possible to have two pie charts in the same layout that work off different fields unless they use the same sort order. When different sort orders are needed to create multiple charts from the same data, what's common practice? Duplicating the table and creating a separate layout? Or is it possible to have multiple layouts for the same table in FMP? I'm confused on this point because the Layout drop down list at the top left of the FMP window seems to actually be a list of tables. Yet I remember in previous editions of FMP, it was possible to have different layouts for the same table. If not, is it possible to create a new table from a result set?

                        Thanks again for all your help, PhilModJunk. If you were here I'd offer you your favorite adult beverage and a meal to go with it ;)

                         

                         

                        • 9. Re: Pie Chart With Summary Fields?
                          philmodjunk

                          As such, it isn't possible to have two pie charts in the same layout that work off different fields unless they use the same sort order.

                          This is only true with the found set/summarized data option.

                          Each wedge might be a different record with a relationship used to summarize the data.

                          You might use ExecuteSQL to produce a series of return separated values to chart using the delimited data data source option.

                          You can also use a different layout  or window to chart data and then use GetLayoutObjectAttribute to copy the chart image into a container field to display the chart as an image. Thus you could sort, set a container field to the image, sort differently set a different container field to that image...