5 Replies Latest reply on Apr 1, 2014 4:13 PM by philmodjunk

    Summarizing Data in a Table

    evanscl

      Title

      Summarizing Data in a Table

      Post

           See screenshot below. I want the sum of four fields (CorvidsReported, CorvidsTested, OtherReported, OtherTested) by County and EpiWeek. Each of the four fields is a calculation field (for example, CorvidsReported = GetSummary(TotalCorvidsReported; EpiWeek) so that each of the four fields is the sum by EpiWeek.

           When I create a layout and add the fields, the data are not being summarized appropriately. For example (see screenshot), "Beaufort County" in EpiWeek "42" is listed 3 times instead of just once as I wish it to do.

           Can you suggest the best way to summarize a field or fields based on multiple fields?

      ReportTable.jpg

        • 1. Re: Summarizing Data in a Table
          philmodjunk

               What kind of field is "EpiWeek"? What does a value in that field mean? Is it a number field or a calculation field?

               Your layout is in table view so each row represents a different record in your table. But without knowing more about an "Epiweek" I can't tell you why you have three records for the same count with the same value in the Epiweek county--which could be either a data entry error, a wrong calculation, a wrong sort order or some combination of the three.

               Instead of a table view, I would us ea list view with a summary report where you can set up sub summary layout parts for specific groups of records such as all records for a given county or EpiWeek.

          • 2. Re: Summarizing Data in a Table
            evanscl

                 EpiWeek is an epidemiological week number (the equivalent of 52 weeks per year, where Week 1 = 1 ..... Week 52 = 52).

                 EpiWeek is calculated from a custom function:

            Let ( [weekno = WeekOfYear ( DateField ) ; firstday = DayOfWeek ( Date ( 1 ; 1 ; Year( DateField )) )] ;
                  
            Case ( firstday > 4 and weekno = 1 ; 53; 
            firstday < 5 and weekno  = 1; 1;
            firstday > 4 ; weekno - 1;
            weekno ) 
            )
                  
                 The EpiWeek is then calculated: EpiWeek = EpiWeek (DateOnset), in which the result is set to a number, but it could easily be stored as text since the week number is not being treated as a number.
                  
                 I have to upload data to CDC for national reporting. The data has to be in a Table, rather than a Report, so that the data can be uploaded. Therefore, I can't use the Sub-Summary Report.
            • 3. Re: Summarizing Data in a Table
              philmodjunk

                   Then you would appear to have 3 records for Beafort County with dates in the Epidemiological Week 42. I can't tell you if you are supposed to have 3 such records or not, only observe that this why you see what you can see here.

                   If your getSummary calculations are just computing sub totals, you may not need them in order to export the needed table of data. There's a grouping option that can export one row of data for each group of sorted records and your summary fields can then be used to export the sub totals If I recall how this works correctly.

              • 4. Re: Summarizing Data in a Table
                evanscl

                     Yes, I have 3 records for Beaufort County with dates in the EpiWeek 42. The data would normally appear in the table as:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                          Birds Reported by County, EpiWeek, and Arbovirus Tested
                                    County                     EpiWeek                     Arbovirus Reported                     Corvids Reported                     Corvids Tested                     Other Reported                     Other Tested
                                    Beaufort County                     42                     WNV                     0                     0                     1                     1
                                    Beaufort County                     42                     WNV                     0                     0                     1                     1
                                    Beaufort County                     42                     WNV                     0                     0                     1                     1

                     As I want to summarize the data, I want the 3 rows above to collapse into one row:

                                                                                                                                                                                                                                                                                                                         
                          Birds Reported by County, EpiWeek, and Arbovirus Tested
                                    County                     EpiWeek                     Arbovirus Reported                     Corvids Reported                     Corvids Tested                     Other Reported                     Other Tested
                                    Beaufort County                     42                     WNV                     0                     0                     3                     3

                     In the database, I have the fields, NumberTested = usually 1, Corvid = Yes/No, and Tested = Yes/No

                       
                •           {Corvid = Yes and Tested = Yes or No} = Corvids Reported
                •      
                •           {Corvid = Yes and Tested = Yes} = Corvids Tested
                •      
                •           {Corvid = No and Tested = Yes or No} = Other Reported
                •      
                •           {Corvid = No and Tested = Yes} = Other Tested
                     

                There's a grouping option that can export one row of data for each group of sorted records and your summary fields can then be used to export the sub totals If I recall how this works correctly.

                Can you tell me about the grouping option that you mentioned or some other way to accomplish summarizing the data?

                • 5. Re: Summarizing Data in a Table
                  philmodjunk

                       What I am describing will not change what you see on your screen, but will enable you to export a single row of data for a group of records.

                       Sort your records to group them the way that you want them to be grouped. Then select Export Records from the FIle Menu and you'll find that the dialog that opens includes a "group by" option that lets you export a single row of data for a group that you identify in this dialog. How you sort your records before selecting this option will affect what grouping options appear in this dialog.