10 Replies Latest reply on Nov 11, 2013 8:42 AM by philmodjunk

    Need help with a pie chart

    SimonSherwood_1

      Title

      Need help with a pie chart

      Post

           Hi

           I'm trying to make a pie chart and need some help.  I have a table of financial transaction where payments are made either by cash, cheque or bank transfer, so there is a payment method field and a payment amount field.  I've made 3 calculation fields that copy the payment amount in depending on what the method is, and then I have 3 more SUM fields to total these calculation fields.

           These transactions are related to another single record global data table by date so I can extract certain transaction per day or week.

           I'm trying to create a pie chart showing percentages of these transactions using the summary fields based upon the relationship with the other table.  I can't seem to find the right edit boxes on the chart setup box or I'm inputting the wrong information.  I seem to be able to get lots of slices on the chart but not just the 3 that I need Cash, Cheque & Bank Transfer.

           Any help much appreciated.

           Simon.

        • 1. Re: Need help with a pie chart
          philmodjunk

               On which table is your chart layout based?

               Do your sum calculation fields return the correct sub totals?

          • 2. Re: Need help with a pie chart
            SimonSherwood_1

                 Thanks for responding, I've checked out the SUM totals of the calculation fields and weirdly enough they don't add up to what I'd expect them to be, they just show the SUM total of all records on that field.  The layout relationship that the chart is based on is based on a date field, I have a date field set to the beginning of each week in the financials table.

                  

                 Simon.

            • 3. Re: Need help with a pie chart
              philmodjunk
                   

                        they just show the SUM total of all records on that field.

                   That description doesn't really make sense. Can you explain what you mean by that?

                   Sum is designed to sum a "list" of values.

                   That list can be explicitly listed as fields from the same record: Sum ( field1 ; field2 ; field3 ) If you just use: Sum ( Field1 ) , sum returns the value of Field1.

                   That list can be the repetitions of a repeating field: Sum ( RepeatingField )

                   That list can be from a set of related records: Sum ( RelatedTable::Field )

                   Only the last example sums data from more than one record. Which example matches what you have tried to use?

              • 4. Re: Need help with a pie chart
                philmodjunk

                     Please note that the sum function is not the only way to get such an aggregate value. A Summary field may also be used to get a total or sub total from a group of records.

                • 5. Re: Need help with a pie chart
                  SimonSherwood_1

                       Sorry the SUM fields are displaying the correct totals mistake on my part, blurry eyes.  

                        

                       Let me work backward through it. I have 3 SUM fields that display totals of 3 calculation fields.  The calculation fields result in a number.  The Number is set in based upon a logical function from 2 fields in the same table.  This is the calculation used "If ( Payment Type = "Cash" ; Payment ; 0)". "payment type" and "payment" are fields on the same table, payment being a number field "payment type" being a drop down value list of "cash, cheque and Bank Transfer"

                       This all works fine, I just need to know how to use the 3 SUM fields in the chart setup box, to create a pie chart.

                       Simon.

                  • 6. Re: Need help with a pie chart
                    philmodjunk

                         Yet there is more than one way you could have set this up and critical details are not clear. To repeat:

                         On what table is your chart layout based? That "Context" will determine options for how you set this up. (What is selected in "show records from" in Layout Setup?)

                         And are your "SUM" fields calculation fields or fields of type summary.

                         Please note that your pie chart is possible by using a single summary field and sorting your records to group them, but that may not fit the "context" in which you are trying to set up your chart.

                    • 7. Re: Need help with a pie chart
                      SimonSherwood_1

                           The Layout setup shows records from reports.  Reports is a table with global data.  The records that the piechart is based on is called Transactions.  Reports and Transactions are related.

                            

                           Simon.

                      • 8. Re: Need help with a pie chart
                        philmodjunk

                             It might be simpler to base your layout on Transactions, A single summary field can then return the subtotals for each wedge.

                             But working with Reports as your layout context:

                             List ( SumField1 ; SumField2 ; SumField3 )

                             can be the calculation you use for your y-series (wedges).

                             List ( "Field1Wedgelabel" ; "Field2WedgeLabel" ; "Field3WedgeLabel" )

                             Can be the x-series (wedge labels).

                             In your data source settings, you'll need to specify that you are using delimited data.

                        • 9. Re: Need help with a pie chart
                          SimonSherwood_1

                               That's exactly what I needed, never heard of the List function, brilliant learn something new every day.

                                

                               Simon.

                          • 10. Re: Need help with a pie chart
                            philmodjunk
                                 

                                      never heard of the List function

                                 The List function is one of the most useful functions available in FileMaker. In Version 12, it's somewhat eclipsed by ExecuteSQL as it can produce the same list, but also with many more tools for controlling what items appear in that list.