10 Replies Latest reply on May 13, 2011 1:00 AM by Bowster

    Total of calculated values

    Bowster

      Title

      Total of calculated values

      Post

      This will be a simple question, sorry.

      Mac OS10.6.7 FM Pro 11

      I have made a report in list view of sorted records and calculated values for those sorted records.  The calculated values are unstored, because they contain a 'GetSummary' function.  I want to find the total of the calculated values.  The Sum Function applied to the name of the calculated value field returns the last value, not the total of all the values.  I am a newbie in FM, but quite good in Access, where I must admit I found this particular exercise a lot easier.  

      I would appreciate someone's guidance on this.

      Many thanks

        • 1. Re: Total of calculated values
          philmodjunk

          Most people that are familiar with Access find learning FileMaker a bit extra frustrating as they have to "unlearn" the Access method even as the learn the FileMaker equivalent (which can be very different) for accomplishing the same thing.

          Much depends on the design of your layout and how you need to organize data in your report. You indicate that you have a list view layout. That suggests that you may not need the GetSummary field unless you are using summary fields in a calculation and need to access subtotals for specific groups of records in your report.

          To get sub totals for your report, you can actually use just the summary field, but place it inside sub summary layout parts. You can then put the same summary field in a grand summary, Header or Footer layout part to display the grand total for all the records in your found set. The trick is to sort your records in a sort order that includes the field specified as the "sorted by" or "break" field in the setup for the sub summary part.

          Here's a tutorial on summary reports you may find worth a look:

          Creating Filemaker Pro summary reports--Tutorial

          Oh yes, the reason Sum didn't work is that it sums a list of values included in the parameter(s) passed to the function and this can be done in several ways:

          Sum ( 4 ; 5 ; 6 ) produces a sum of 15
          Sum ( Repeatingfield ) sums the repetitions of the repeating field
          Sum ( RelatedTable::NumberField ) sums up all the values of NumberField for all related records

          What you tried was essentially Sum ( 4 ) which produces the answer 4. Wink

          • 2. Re: Total of calculated values
            Bowster

            Very many thanks for this, Phil.  I have had a look at the tutorial, but it doesn't seem to address the problem I have.

            My database is composed of 'Owners' who each have a number of assets.  I sort the Owners alphabetically and then refine my search to select certain Owners and certain of their assets.  My report then lists against each of the selected Owner, the total number of selected assets and the total of one of the measures of the selected assets.  So far, so good.  I can produce this report in List View, the total number of selected assets and total of one of the measures put into Part 'Sub-summary by owner (trailing)' and the grand total for both columns into 'Trailing Grand Summary'.

            I then want to make a further calculation on the result of the total of the certain measure.  I do this by using the 'Get Summary' and the 'If' functions.  The result of this calculation is against each owner, again in the Part 'Sub-summary by Owner (trailing).  This works fine (after a lot of experimentation and reading the 'help' file), and the correct answers are shown in the report.  But now I want to calculate the total of these calculated results.  As discussed, I have tried the 'Sum' function, but this returns only the last calculated result.  

            All I want to do is to add up the total of the column that appears in my List View.

            I would appreciate any help you can give me.  This should be simple, but is maddeningly difficult to find the right answer to.

            • 3. Re: Total of calculated values
              philmodjunk

              You haven't spelled out all the details here, but you appear to have a calculation field given your references to getSummary and If functions. You can define a summary field that computes the total fo this calculation field and it should produce the totals (and sub totals in sub summary parts), as needed for this report.

              • 4. Re: Total of calculated values
                Bowster

                I'm afraid this doesn't work, Phil, unless I am not doing it right.  I have tried Sum(fieldname), Sum(calculation), etc, in the 'Trailing Grand Summary' part but I either end up with the last calculation or a figure that seems to add up every number used in the process, not the total of the calculation results.

                If I may, I will send you a copy of my calculation later.

                Many thanks for this advice.

                • 5. Re: Total of calculated values
                  philmodjunk

                  I'm not suggesting that you use Sum in a calculation field. I'm suggesting using a "total of" summary field to total the calculation field you have created.

                  • 6. Re: Total of calculated values
                    Bowster

                    I'm sorry, Phil, I was not clear earlier.  Even if I Total of 'fieldname', I get a weird answer, almost as if the 'Total of' has used every figure used in the calculation.  I have even run a 'running total' alongside each calculation to see what might be going on, but this effectively doubles each calculated result.

                    My calculation is effectively the following:

                    'Calculation = If(GetSummary (Total measure ; Owner) > 500000 ; (If Manager = "Yes" ; 500000*2/3 ; 500000) ; GetSummary (Total measure ; Owner))'

                    This produces the correct answers for each entry, but my difficulty is in finding the Grand Total of all the calculation results.

                    Very many thanks

                    • 7. Re: Total of calculated values
                      philmodjunk

                      If you name this field cCalcField, a summary field that computes the total of cCalcField should return the grand total, provided your records are sorted by Owner.

                      Also, make sure the calculation's return type is number and not text.

                      • 8. Re: Total of calculated values
                        Bowster

                        No. I'm sorry, Phil, but this doesn't work.  I end up with a total that is way in excess of the actual result (around 228 million as opposed to 8 million).  it is almost as if the Total of calculation has included every figure that is used in each calculation.

                        I really appreciate your assistance with this,

                        • 9. Re: Total of calculated values
                          philmodjunk

                          That's exactly what it does. I gather you have something like:

                          Group 1, Group 1 subtotal calc
                            Group 1 records listed here
                          Group 2, Group 2 subtotal calc
                             Goup 2, Records listed here

                          And you need one value for group 1 added to one value for group2.

                          "This produces the correct answers for each entry," misled me as it appers you really mean that it computes the correct subtotal for each group.

                          You'll need a much more sophisicated calculation to total up to get the correct grand total here that adjusts the individual values by number of records.

                          Add a summary field, sCount that's the "count of" a non blank field in your table.

                          Define a modified calculation such as:

                          If(GetSummary (Total measure ; Owner) > 500000 ; (If Manager = "Yes" ; 500000*2/3 ; 500000) ; GetSummary (Total measure ; Owner)) / GetSummary ( sCount ; Owner )

                          And then your grand total summary field can compute the "Total of" this new calculation field.

                          • 10. Re: Total of calculated values
                            Bowster

                            Very many thanks, Phil.  I will try it and let you know