8 Replies Latest reply on Dec 19, 2011 2:05 PM by wjpatterson937

    I can find it, I cannot figure out how to summarize it!

    wjpatterson937

      I am working of a report which contains numerous date fields.

       

      I have establshed four date fields as the critical tracking points.

       

      I want to summarize the values for each of the fileds by week to show the volume of projects passing through a particular point in time (a week which is genrated by using the "WeekOfYear" calculation on each of the four fields.

       

      I get the results I want at that stage, a single integer representing the week value of a given production process.

       

      I have a layout with the four fields sitting side by side so I can see when an individual project reached each phase of production.

       

      I can search a given field and find out how many projects were "shipped" in week 15 of the year.

       

      What I am after is a layout which will display that kind of summary information for each of the four processes. A example simple table follows:

       

      WeekProject CreatedProject ApprovedProject to VendorProject shipped
      15421241229177
      16492263244168

       

      This is a device for looking back and establishing a pattern for annual volume.

       

      When I sort the contents based on a single common week the column returns a common value because all the records have data at that point in the process, whatever I sort by returns the appropriate records but the summary field, but my summary field counts the records as identical because the field has a value in record.

       

      To get the above information I went into the table and "found" for the number of records with a "Project Created" value of 15, then found the number of records for "Project Approval" with a value of week 15, and so on.....

       

      I have a sense the answer is within my grasp but I am missing something. I can calculate the week a job reached a certain phase, but when I create a summary field to count the records, it accurately counts the found set, which is based on a common value for the week.

      I believe I have tried all the options for the summary field. I can imagine a script to generate and record the numbers, but I would rather solve the problem functionally rather that resort to brute force processing by script.

        • 1. Re: I can find it, I cannot figure out how to summarize it!
          FCallanan

          Hi WJ,

           

          If I understand you correctly, the report you're looking for would be cross-tabulated.

           

          The way to achieve that sort of report is to actually create another table for your report. You'd also create a script to populate the table and a layout to display it. Each time you want an updated report, you just run the script.

           

          When you create your new Reports table in the Magae Database dialog, you'd define fields like Reports::Week, Reports::Projects Created, Reports::Projects Approved, Reports::Projects to Vendor, Reports:: Projects Shipped, and so on. In this scheme, the number of records in each report would be equal to the number of weeks for which you are reporting.

           

          The script which populates the report records would do pretty much what you describe doing manually, including making a record of the results. Something like the pseudo script below:

           

          Find all records between report start date and report end date.

          Sort by Week

          Go to the first record

          ## Declare some variable to count the results as you visit each record (a good praactice, though optional)

          Set variable [$thisweek_create = 0 ]

          Set variable [$thisweek_approve = 0 ]

          Set variable [$thisweek_vendor = 0 ]

          Set variable [$thisweek_ship = 0 ]

          Set variable [$rptweek=Table::0]

          Loop

              If( Table::Week = $rptweek )  // check that this record is the same week

                  ## increment each report category for the week, if the record meets the criteria*

                  Set variable [$thisweek_create = $thisweek_create + Table::Project Created ] //see *

                  Set variable [$thisweek_approve = $thisweek_approve + Table::Project Approved ]

                  Set variable [$thisweek_vendor = $thisweek_vendor + Table::Project to Vendor ]

                  Set variable [$thisweek_ship = $thisweek_ship + Table::Project Shipped ]

              Else  //this record is now the next week in the sort order

          ## Since the previous week is complete, write the variables to a report record.

                  Go to Layout (Cross-tab report fields )

                  New record

                  Set Field [Reports::Week ; $rptweek ]

                  Set Field [Reports::Projects Created ; $thisweek_create]

                  Set Field [Reports::Projects Approved ; $thisweek_approve]

                  Set Field [Reports::Projects to Vendor ; $thisweek_vendor]

                  Set Field [Reports:: Projects Shipped ; $thisweek_ship]

          ##You've captured the counts in a record; re-set the counters: starting values come from this record

          ## Important. Go to the original context to continue looping thru the rest of the found set.

                  Go to Layout (Original Layout )

                  Set variable [$rptweek=Table::Week]

                  Set variable [$thisweek_create =  Table::Project Created ]

                  Set variable [$thisweek_approve = Table::Project Approved ]

                  Set variable [$thisweek_vendor = Table::Project to Vendor ]

                  Set variable [$thisweek_ship = Table::Project Shipped ]

              End If

          Go to record [Next, Exit after last]

          ## preview the report

          Go to Layout (Cross-tab Report_print )

          Sort (by Week)

          Enter preview Mode

           

           

          I think that's the gist of it.

           

           

          *Note that my-pseudo calculation assumes "1" is the value entered in "Project Created". So the calc just adds it to the existing variable. If your fields have a "Yes" or some other value, the Set Variable calcs might look like this:

                  Set variable [$thisweek_create = $thisweek_create +

                                          If(Table::Project Created="Yes" ; 1 ; 0) ]

                 

           

          If you haven't done one of these yet, you'll be amazed at how versatile the technique is. You may have to tweak it a bit until you're satisfied you're getting accurate results. The data viewer is very handy for checking your calcs record by record, if need be.

           

            

          Frank Callanan

          Camden, Maine

          • 2. Re: I can find it, I cannot figure out how to summarize it!
            FCallanan

            I forgot to mention... In the Reports table, do create a record ID field, so that all the records that comprise one report will be identifiable. I usually declare a variable ( $reportid ) before the Loop begins so that value is available for every report record. Insert the Set Field step along with the other set field steps, somethiing like

            Set Field (Reports::report ID =$reportid )

             

            Frank Callanan

            Camden, Maine

            • 3. Re: I can find it, I cannot figure out how to summarize it!
              wjpatterson937

              I will give a try in the morning.

               

              I have been reading about cross tabulated reports and what I have seen so far calls for a calculation and summary filed for each cell per each line.

               

              So a summarized report with weekly totals sound like roughly 100 fields or so.

               

              Thanks for your assistance and I will report back tomorrow.

               

              Bill

              • 4. Re: I can find it, I cannot figure out how to summarize it!
                FCallanan

                wjpatterson937 wrote:

                 

                I have been reading about cross tabulated reports and what I have seen so far calls for a calculation and summary filed for each cell per each line.

                Yes. Though in my above example that would only be about dozen fields,for 6  report categories. See sample file attached.

                 

                 

                 

                Frank Callanan

                Camden, Maine

                • 5. Re: I can find it, I cannot figure out how to summarize it!
                  LyndsayHowarth

                  I wouldn't do it with FInds... I would do it with relationships... then all you would have to change is a week number and the whole thing would update on commit.

                   

                  A week (of the year...) has a min/Sunday date and a max/Saturday date. You are therefore able to set up relationships where the dates are ≥ and ≤ the values entered into 2 global fields populated by choosing week.

                   

                  The weeks would be not of any found set... because the date of creation is different from date of approval as are date to vendor and date shipped... So week 15 for created date might have approvals for week 13 etc. Therefore each relationship might be between the 2 dates and "week of approval" and "week shipped to vendor" etc

                   

                  I have to go cook dinner... prod me for more info if this doesn't make sense.

                   

                  - Lyndsay

                  • 6. Re: I can find it, I cannot figure out how to summarize it!
                    wjpatterson937

                    First off,

                     

                    Thanks. It is so nice to work with a community and have problem solving by experts all around the world like yourself.

                     

                    I like the relationship angle and for a weekly report I get the approach.

                     

                    If I ant to summarize and entire year by weeks 1,2,3 and so on in a single table it would seem to me I would still require a script to run the recurring portion of the process.

                     

                    Am I seeing the solution correctly or is there something I am not seing.

                     

                    Thanks Again,

                     

                    Bill

                    • 7. Re: I can find it, I cannot figure out how to summarize it!
                      LyndsayHowarth

                      Hi Bill,

                       

                      No... no scripts.. apart from one which might commit the dates entered using script triggers.

                       

                      You have a popup field with a week number (I will leave out the issues of the year at this point) attached to a global field for week number in the table where you are going to display the summary.

                       

                      In the table which contains the data (it could be the same table) you create a field for each of the date-types which calculates the week of the year.... one for each.

                       

                      You then create a relationship between your global field and an instance of the Project table for each of the different date-types matching to the associated week number... so you have:

                      global_table::Global week Num = Project by created week::created week

                      global_table::Global week Num = Project by approval week::approval week

                      global_table::Global week Num = Project by vendor week::vendor week

                      global_table::Global week Num = Project by shipped week::shipped week

                      Your Project table should have a count ('created date') (plus any summary fields for total or average you may need for other purposes.)

                       

                      Then on the layout you put the global field and add a valuelist of 1-53

                       

                      Then you add the count field from each of the separate relationships.

                       

                      Now... that gives you one line of data..... You get the result each time you change and commit the value for week num

                       

                      The variation that YOU want... would be to have a week table with 53 records and the week num field as a per-record value of the record number... use the same relationships... you can just show the records as a list and achieve the effect in your example...

                       

                      HTH

                       

                      - Lyndsay

                      • 8. Re: I can find it, I cannot figure out how to summarize it!
                        wjpatterson937

                        Your solution works beautifully.

                         

                        I cannot thank you enough or tell you how much I admire your understanding of the subtlety of relationships.

                         

                         

                        So let me confirm your assumptions and ask more questions.

                         

                        The year is not of issue for now. I can get rid of any records which do not fit the profile.

                         

                        I created a new table called Global_table.

                         

                        In Global_Table I create a field called "Globa_Week_Number".

                        The field is a number and has a global value.

                        I create a value list of integers and attach that to "Global_Week_Number".

                         

                        I had created a field in the original project table which computes the "Week Value" for each of the four key dates "Create Week", Approve Week" and so on.

                         

                        I have matching summary fields to count the results of the fields computing the "Week_Value".

                         

                        I create an instance of the main project table for each of the four key dates Values

                         

                        I linked the "Global_Week_Value" on each of the four instances to a single matching core field:

                         

                        The relationships appear just as in your reply.

                         

                        I created a layout with the Global_Table as the reference table.

                         

                        When I added the fields from each instance of the main project table I originally got an "Index missing" message for each field.

                         

                        As I changed the Indexing on each field, the proper values appeared.

                         

                        I am not sure why when I created the original fields I switched on "do not store results", (I think it was the because the "Ship Date" was evaluating a constructed date built with nested "IF" statements and Filemaker complained so I set them all up that way).

                         

                        There are many lessons learned in this event:

                        a reminder of how nice it would be to know what one is expected to build before one starts (every project dream),

                        a reminder about the power of numbers and the numbers of hands and minds real problem solving involves,

                        and how good it must feel to be able to pay forward some of the really fine training you have received.

                         

                        I hope our paths cross again