13 Replies Latest reply on Jul 5, 2011 3:00 PM by philmodjunk

    Summary of Multiple Calculated Fields

    MAM

      Title

      Summary of Multiple Calculated Fields

      Post

      I have three groups of fields that are calculated for disputes.  One calculation determines the service, one calculation determines the age of the dispute and the third calculation determines the dollar amount range of the dispute.  There are two things I must report.  One is the age of the dispute by service and the other is the age of the dispute by dollar amount range.  If I could figure out how to determine one of these, I could do the other.  I've been working for hrs.  I've done several forums and Help searches and can't quite find what I need.  Sure hope someone can help.
      Thanks,
      Mary Ann

        • 1. Re: Summary of Multiple Calculated Fields
          philmodjunk

          Sounds like you need two summary reports one that groups by Service and one that groups by dollar range.

          Using Group by Service as our example:

          Use a list view for this report.

          In layout mode, add a sub summary part using part setup... from the layouts menu. Choose your Service field as the "when sorted by" field in this sub summary part. Choose "print above" to locate this sub summary part above the body layout part

          Put the service field inside this sub summary part and put your individual data fields inside the body. If there is a total, average, or other computation for each service that you need in this report, define a field of type summary for this purpose and add it either to this same sub summary part or to an additional sub summary part set to "print below" the body.

          Now save your changes, perform a find to find the records you want for your report and then sort them by your service field. For FileMaker 10 and newer, you are done. If using an older version, you'll need to enter preview mode to see the sub summary parts.

          • 2. Re: Summary of Multiple Calculated Fields
            MAM

            Still not what I need.  I've enclosed from Filemaker what the report has to look like.  I've been doing manual finds of "Y" in Service and Age and filling in a spreadsheet and then going to the next Age until I've recorded all and then I move on to the next service and recording all the ages.  Then I move to the dollar range and age.  This manual effort is taking way too long and I can't believe that Filemaker can't do this for me.  Now I will say I have an older version (Pro 7) but I still think this is possible.  I already had figured out how to get single totals as the enclosed number come from my calculations and are not manually entered.  My version do not give me the option of "Print Above" so maybe that is why I can't get this to work.

            • 3. Re: Summary of Multiple Calculated Fields
              MAM

              Sorry couldn't find a place to upload document

              • 4. Re: Summary of Multiple Calculated Fields
                MAM
                A
                Service 
                B
                Service
                C
                Service
                D
                Service
                E
                Service

                F    Totqls
                Service

                 Age # of Disputes 0-$5,000 $5,000-$15,000 $15,000-$25,000 $25,000-$50,000 over $50,000
                5178 196 7876 1 7 3474   16732 16671 59 1 0 1
                0 0 0 0 0 0    0-15 0 0 0 0 0 0
                25 33 132 0 0 550    16-45 740 739 1 0 0 0
                0 0 0 0 0 0    46-60 0 0 0 0 0 0
                23 20 112 1 7 499    61-90 662 661 1 0 0 0
                5130 143 7632 0 0   2425     90+ 15330 15271 57 1 0 1

                This is what the final report lools like.  Those in red I've already managed to have Filemaker product.

                • 5. Re: Summary of Multiple Calculated Fields
                  philmodjunk

                  Always let people know you are using FileMaker 7. It doesn't alter my suggestions in this thread, but it easily could have as newer versions of FileMaker offer options you won't have for doing this in your version of FileMaker.

                  In FileMaker you can work with rows (records) much more easily than you can with columns (fields) when producing this kind of report.

                  What does each row in this table represent?

                  F = 3474 in row one when you have 16732 disputes in # of disputes. Shouldn't the sum of services A through F equal the total number of services?

                  (I'm trying to understand how the data in different columns relate to one another here.)

                  Does the grid you've posted represent the way the data is stored in your file or just the format you need for your report?

                  Please note that a report with this format:

                  Service A:  5178
                  Service B:  196
                  Service C:  7876
                  Service D:  1
                  Service E:   7

                  Total service: 3474

                  Takes much less effort to set up in FileMaker Pro.

                   

                  • 6. Re: Summary of Multiple Calculated Fields
                    MAM

                    When I pasted from Excel things got a little out of alignment.  The 3474 is Service F and the Total is 16732.  This is how the report has to look not how the data is set up.  Each dispute has it's own record.  I need to be able to do this report without doing finds.  That is what I'm doing today.  Each record has an age and dollar amount.  Here are the calculations I'm using for each Age range:
                    If ( Dispute Age  ≤ 15; "Y"; "")
                    If ( Dispute Age > 15 and Dispute Age ≤ 45; "Y"; "")
                    If ( Dispute Age > 45 and Dispute Age ≤ 60; "Y"; "")
                    If ( Dispute Age > 60 and Dispute Age ≤ 90; "Y"; "")
                    If ( Dispute Age  ≥  90; "Y"; "")

                    Here are the calculations I'm using for each dollar range:
                    If ( DISPUTED_AMT  ≤ 5000; "Y"; "")
                    If ( DISPUTED_AMT > 5000 and DISPUTED_AMT ≤ 15000; "Y"; "")
                    If ( DISPUTED_AMT > 15000 and DISPUTED_AMT ≤ 25000; "Y"; "")
                    If ( DISPUTED_AMT > 25000 and DISPUTED_AMT ≤ 50000; "Y"; "")
                    If ( DISPUTED_AMT   ≥  50000; "Y"; "")
                    I originally had the Service set up the same way using the "Y" but have since change that to:
                    If ( SERVICE_TYPE  = "TTVC" or SERVICE_TYPE  = "MAAC"; "A") & If (SERVICE_TYPE = "TIIP"; "B") & If (SERVICE_TYPE = "TINI"; "C")& If (SERVICE_TYPE = "MACH"; "D")& If (SERVICE_TYPE = "SSME"; "E")& If (SERVICE_TYPE = "WICP"; "F")

                    I've been doing a Find on the Service "A" and 0 to 15 Age "Y".  I repeat that find for each Age and each Service and record each result in Excel.  I then go thru the same process for the Amount and Age.  As you can imagine this is quite time consuming.  I want to have this report without doing any finds and print it to PDF instead of using Excel.  It has to be in the format shown.

                    • 7. Re: Summary of Multiple Calculated Fields
                      philmodjunk

                      I still don't have an answer to this question: "What does each row in your example represent?" Different time periods or ...?

                      What you are describing is sometimes called a "Cross tab report". It's not the easiest thing to set up in FileMaker but it can be done. I'm going to show you how to set up a few of the columns in your report and then hopefully you can extend the process to add all the columns of data that you need. But before I can do that, I need to know what each row in your example represents.

                      • 8. Re: Summary of Multiple Calculated Fields
                        MAM



                        The spreadsheet is divided into two sections. On the Left side the first row headers represent  the services, A thru F.  On the Right side the first row headers represent the dollar amounts of 0 to 5000 thru Over 50,000.  In the Center going down is the Aging of 0 to 15 days thru Over 90 days plus a column with Totals of across each row.  The first row of data is totals.  Total of Service A thru Total of Service F on the left side.  Total of Dollar Amount from0 to 5000 thru Total Dollar amount Over 50,000.  When I paste this there are many rows before the data.  I've tried a dozen ways to get this info in here but nothing works.



                        Here is the
                        Left Side of my spreadsheet





                         
                         
                         
                         
                         
                         
                         
                         


                         
                         
                         
                         
                         
                         
                         
                         


                         
                         
                         
                         
                         
                         
                         
                         


                         
                         
                         
                         
                         
                         
                         
                         


                         
                         
                         
                         
                         
                         
                         
                         


                         
                         
                         
                         
                         
                         
                         
                         


                         
                         
                         
                         
                         
                         
                         
                         


                         

                        Service A


                         

                         

                        Service B


                         

                         

                        Service C


                         

                         

                        Service D


                         

                         

                        Service E


                         

                         

                        Service F


                         

                         

                        Age of Dis


                         

                         

                        Total #


                         

                         

                        5178


                         

                         

                        196


                         

                         

                        7876


                         

                         

                        1


                         

                         

                        7


                         

                         

                        3473


                         

                          
                         

                         

                        167732


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0-15


                         

                         

                        0


                         

                         

                        25


                         

                         

                        33


                         

                         

                        132


                         

                         

                        0


                         

                         

                        0


                         

                         

                        550


                         

                         

                        16-45


                         

                         

                        740


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        46-60


                         

                         

                        0


                         

                         

                        23


                         

                         

                        20


                         

                         

                        112


                         

                         

                        1


                         

                         

                        7


                         

                         

                        499


                         

                         

                        61-90


                         

                         

                        662


                         

                         

                        5130 


                         

                         

                        143


                         

                         

                        7632


                         

                         

                        0


                         

                         

                        0


                         

                         

                        2425


                         

                         

                        +90 


                         

                         

                        15330


                         



                        . Here is the
                        Right Side of my spreadsheet





                         
                         
                         
                         
                         
                         
                         
                         


                         
                         
                         
                         
                         
                         
                         
                         


                         
                         
                         
                         
                         
                         
                         
                         


                         
                         
                         
                         
                         
                         
                         
                         


                         
                         
                         
                         
                         
                         
                         
                         


                         
                         
                         
                         
                         
                         
                         
                         


                         
                         
                         
                         
                         
                         
                         
                         


                         

                        Age of Dis


                         

                         

                        Total #


                         

                         

                        0-5000


                         

                         

                        5000-15000


                         

                         

                        15000-25000


                         

                         

                        25000-50000


                         

                         

                        Over 50000


                         

                         

                        Total #


                         

                          
                         

                         

                        167732


                         

                         

                        16671


                         

                         

                        59


                         

                         

                        1


                         

                         

                        0


                         

                         

                        1


                         

                         

                        167732


                         

                         

                        0-15


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        16-45


                         

                         

                        740


                         

                         

                        739


                         

                         

                        1


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        740


                         

                         

                        46-60


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        61-90


                         

                         

                        662


                         

                         

                        661


                         

                         

                        1


                         

                         

                        0


                         

                         

                        0


                         

                         

                        0


                         

                         

                        662


                         

                         

                        +90 


                         

                         

                        15330


                         

                         

                        15271 


                         

                         

                        57


                         

                         

                        1


                         

                         

                        0


                         

                         

                        1


                         

                         

                        15330


                         



                        So Service A
                        has 5130 disputes that are over 90 days old and 15271 of all the disputes are
                        between 0 and 5000 dollars and over 90 days old.  There is no relationship between service and
                        dollar amount.  There are two
                        relationships.  One between Service and
                        Age and the other is Dollar Amount and Age.

                        • 9. Re: Summary of Multiple Calculated Fields
                          philmodjunk

                          I'm afraid you misunderstood my question. What you call "row headers" are column headers. Rows are horizontal (side to side). Since your examples show multiple rows, I would like to know what each row represents in this report.

                          • 10. Re: Summary of Multiple Calculated Fields
                            MAM

                            Sorry for the confusion.

                            Row 1 Headers
                            Row 2 Totals
                            Row 3 0 to 15 days
                            Row 4 16 to 45 days
                            Row 5 46 to 60 days
                            Row 6 61 to 90 Days
                            Row 7 Over 90 Days
                            the right side of my spreadsheet represents dollar amounts and the left side represents the number of disputes.  I really don't need this in one report but it would be nice if I could have one report for the number of disputes by service and a second report of number of disputes by dollar range.

                            • 11. Re: Summary of Multiple Calculated Fields
                              philmodjunk

                              Ok, that confirms my guess that each row represents a time period, with a "grand total" row thrown in just to make this interesting.

                              We'll need a "report table" where each record is one of these 15 day intervals. Before I dive into the details here, I need to check on some features to see if you have them or not in FileMaker 7:

                              Can you link more than one pair of fields in a relationship? (Can you drag from a Field in Table A to a Field in Table B, then drag from a different field in A to a field in B so that you now have a relationship based on 4 fields instead of 2?)

                              Can you use inequalities in your relationships? (Double click the line linking two related tables. Does the dialog that pops up allow you to use <, > etc. and not just =?)

                              I think these options where added with the release of 7 but don't have a copy of that version that I can test here to be sure. Those features will make setting up relationships for your report easier if you have them.

                              • 12. Re: Summary of Multiple Calculated Fields
                                MAM

                                I've never build a relationship table before.  I know you are saying Oh crap but I have a coworker that has been trying to help me with this and he has done them.  So in answer to you first question I don't know but I have another database the coworker built for me that has a relationship table and I can do < and >.  He also has Filemaker 10 that I'm sure has these features.  I know this won't be easy but I will try to have him look at your first question in v10 and if so I'll have him help me or build it in his version.  He has done this for several other dbs I use.  Thanks

                                • 13. Re: Summary of Multiple Calculated Fields
                                  philmodjunk

                                  I didn't say "relationship" table. I said "report" table. This is just another table in your database, nothing really different about it when it comes to defining it and linking it to other tables in your system. Each record in this table will represent a specific 15 day period so that the relationship between this table and your current table will correctly match to only records from this 15 day interval.

                                  Here's an example of how to get columns A and B to appear in your report. I don't know exactly what value is stored in your table to identify a service of type A and a service of type B so I will assume that you just have the letter "A" for service type A and the letter "B" for service type B reports.

                                  Define a Field, Date1 of type Date. Add a calculation field, cDate2 defined as Date1 + 15 and select Date as the return type. Add two text fields: ServiceA and ServiceB. Define Service A to auto-enter the letter A. Define Service B to Auto-enter the letter B.

                                  Define these two relationships from your report table to different table occurrences of your original table, which I will call ServiceRecs here.

                                  ReportTable::Date1 < ServiceRecsA::DateField AND
                                  ReportTable::Date2 > ServiceRecsA::DateField AND
                                  ReportTable::ServiceA = ServiceRecsA::ServiceType

                                  ReportTable::Date1 < ServiceRecsB::DateField AND
                                  ReportTable::Date2 > ServiceRecsB::DateField AND
                                  ReportTable::ServiceB = ServiceRecsB::ServiceType

                                  Now you can define a pair of calculaiton fields that will return the count of records of service type A and service type B:

                                  Count ( ServiceRecsA::DateField )   // the number of records in this date interval that are service type A
                                  Count ( ServiceRecsB::DateField )   // the number of records in this date interval that are service type B

                                  To get the rest of the columns in your report, you would continue this pattern where you first define a relationship that combines the date interval with another field that identifies the category, then you add a calculation field that refers to this new table occurrence in order to count the records. (There are other functions besides count that can compute a sum, average or even a standard deviation you can use here.)

                                  To get your grand totals, define summary fields in this new table to compute the total of each of these new calculation fields.

                                  To set up your report table, you'd create a group of records, entering the desired date into the Date1 field of each record.