7 Replies Latest reply on Feb 27, 2013 7:21 AM by SandyHaaf

    Summaries based on value

    SandyHaaf

      Title

      Summaries based on value

      Post


           I have a table that has windspeed, rpm, current, and voltage.   What I want to do is create a summary report that will average RPM, Current, and Voltage based on the windspeed for a particular day.    I also need to count the number of times that the RPM, Current, & Voltage value goes above a specified value for each wind speed.

           So, basically for windspeed 4.5-5.5 the show the average of each of those values, plus a count of how many times it goes above a value.  Then again for windspeed 5.5-6.5, etc. all the way up to 39.5-40.5.

           I want the report to look something like this (so each row displays the summaries for that value):

                                 RPM Ave     RPM Count     Voltage Ave    voltage count     Current Ave   Current Count  

           4.5-5.5              150             84                         142                 80                         1.5                  20

           5.5-6.5              162             55                         125                 99                          1.8                 46  

           6.5-7.5              142             11                         130                  22                          2.1                55  

           ...

           39.5-40.5         155             12                          147                  65                         1.2                 22 

            

           What would be the best way to accomplish this.  I'd appreciate any advice and guidance.

            

           Thanks,

           Sandy

        • 1. Re: Summaries based on value
          philmodjunk

               Use a summary report where each row of data shown in your example is displayed in a Sub Summary layout part.

               You can set up a calculation field as the "when sorted by" field to group your records by the ranges shown in the first column. I'm guessing that the first column shows a windspeed range.

               Case ( WindSpeed < 5.5 ; 1 ;
                           WindSpeed < 6.5 ; 2 ;
                           WindSpeed < 7.5 ; 3 ; //and so forth....
                         )

               You can sort your records by this calculation field to group them for your report.

               Summary fields can give you the totals averages and counts for each sub summary group.

          • 2. Re: Summaries based on value
            SandyHaaf

                 That worked perfectly.  Thanks.

                  

                 Now I need to add a field to this report (again for each wind speed category) that counts each RPM value that is > 100 then devide that by the total number of records for that wind speed and multiply by 100 (make it a percent).  I would need to do this for Voltage & Current too.

                  

                 So I want the report to look like this

                  

                  

                 WindSpeed     RPM Ave     RPM Count    RPM Exp     Voltage Ave    voltage count     Current Ave   Current Count  

                 4.5-5.5              150                 84                    80%             84                         142                 80                         1.5                  20

                 5.5-6.5              162                 55                     0%              55                         125                 99                          1.8                 46  

                 6.5-7.5              142                 11                    -10%            11                         130                  22                          2.1                55  

                 ...

                 39.5-40.5         155             12                         5%               147                        65                     1.2                 22 

                  

                  

                 I'd appreciate your help.

                  

                 Thanks,

                 Sandy 

                  

            • 3. Re: Summaries based on value
              philmodjunk

                   Let me first be sure that I understand:

                   Say  you have 30 records in the 4.5-5.5 group. 5 of those records have RMP values > 100 so the value would be computed as (5 / 30 ) * 100 or 16.67%.

                   Is that correct?

              • 4. Re: Summaries based on value
                philmodjunk

                     Also, is this report based on all records in the table or do you perform a find to pull up just certain records? (Such as all records for a given range of dates...)

                • 5. Re: Summaries based on value
                  SandyHaaf

                       Yes, your example is exactly right.  (Eventually I may need to do another calculation on this, but for now that is what we are looking for).

                        

                       As for your question regarding whether it is being performed on all records or just some.  This hasn't been decided yet.  

                       Each day we will be importing the day befores data into the database and then running this report (it will tell us if everything is functioning correctly or not).   I'm thinking it may be easier (and faster) to import the data into a temporary table, that just has that days data into it.  Then, after the daily report is done, then move that days data into the archival table.

                        

                       Do you think that makes sense?  It there is a preference for running this report, please let me know.

                        

                       Thanks,

                       Sandy

                        

                  • 6. Re: Summaries based on value
                    philmodjunk

                         I recommend that you take a careful look at running your report from a table that contains all of your records. You may not need it for day to day reporting, but it gives you the option of using the same layout to examine historical data or data over a longer time period than is normally used in your reports.

                         I was thinking in terms of a self join to match to records of the same windspeed category, but on further thought, the method I am about to suggest will work correctly whether your report is based on all records or just some records.

                         Define a calcualtion field for RPMs such as:

                         RPMfield > 100

                         Select number as the return type. This will show 1 if the value in RPMfield exceeds 100. (RPMfield must be of type number, not text!)

                         Define a summary field, sOver100, as the Total of this new calculation field and it will compute the count of how many records exceed 100 RPM.
                         Define a second summary field, sCount, as the Count of any field in this table that always has data in every record. It will give us the total count.

                         To get your % value, use this calculation in a calcualtion field:

                         GetSummary (sOver100 ; cWindspeedCategory ) / GetSummary (sCount ; cWindspeedCategory )

                         cWindspeedCategory is my name for the calculation field using the case function that I suggested back at the start of this thread.

                         Select number as the return type.

                         When you place this field on your report layout, use data formatting in the inspector to format the result as a percent and to specify the desired number of decimal places.

                         This same approach can be extended to each of the other similar columns in your report.

                    • 7. Re: Summaries based on value
                      SandyHaaf

                           Thank You!!!  This works great!  And I've learned so much.  I couldn't of done this report without your help.

                           Cheers,

                           Sandy