13 Replies Latest reply on Mar 15, 2017 12:43 PM by icotta

    Sub-Summary with Calculations

    icotta

      I have a layout that includes fields (which are all on the same table)  for participants, bus number, am absence codes and pm absence codes.  It has a sub-summary sorted by bus number printed above the fields.  I have another sub-summary that prints below and has the total number of participants for each bus.  This sub-summary shows the number of participants on each bus.  The problem is that I need this number to be  the larger number of the participants minus the number of am absence codes or participants minus the pm absences.

       

      For example there may be 50 total participants on the Monday Daily Sheet. Each sub-summary have the total of participants on each bus which total the 50. There are 42 participants with entries in AM absence codes and 38 with entries in pm absence codes.  I need to have the sub-summary total show the break down on the 42 (the larger of the .participants without an absence code).  The totals for am and pm absences are not static so the data changes daily.

       

      I have tried several ways to get this information and have been unable to do so.  I know there is a way to do this but it is really above my level of experience. Any assistance will be appreciated.

        • 1. Re: Sub-Summary with Calculations
          philmodjunk

          Do you have separate fields for the am and pm absence codes?

           

          If so, summary fields that count the am absence and pm absence fields can produce the needed values for a calculation field that uses GetSummary to get the needed subtotals for participants, am absent and pm absent.

           

          Let ( p = getSummary ( sParticipantsTotal ; BusNumberField ) ;

                 Max ( p - GetSummary  (sAmAbsentTotal ; BusNumberField ;

                           p - getsummary ( sPmAbsentTotal ; BusNumberField )

                         ) // Max

                ) // Let

          • 2. Re: Sub-Summary with Calculations
            icotta

            Yes, they are separate fields.  I am fairly new to all of this. I will work with the calculation you provided and see if I can get it to work.  I really appreciate your help!

            • 3. Re: Sub-Summary with Calculations
              icotta

              I have the following fields:

              Summary Fields:

              PMAbsenceTotal  = count of number_bus

              AMAbsenceTotal  = count of number_bus

              ParticipantTotal  = count of number_bus

               

              Calculation Field:

              ParticipantDailyTotal

              Let ( p = GetSummary ( ParticipantTotal ; number_bus ) ;

                     Max ( p - GetSummary  (AMAbsenceTotal ; number_bus );

                               p - GetSummary ( PMAbenceTotal ; number_bus )

                             ) // Max

                    ) // Let

              I do a find for Monday,  where code_absence_am is blank, sort by number_bus  that shows a total record of 38

              I do a find for Monday,  where code_absence_pm is blank, sort by number_bus  that shows a total record of 42

              When I go to the layout with the summary field the result of 0 is shown for each bus.

               

              I know I must be doing something wrong.  How do I capture and keep the information for each of my finds?

               

              Sorry to be so dense!

              • 4. Re: Sub-Summary with Calculations
                philmodjunk

                PMAbsenceTotal  = count of number_bus

                AMAbsenceTotal  = count of number_bus

                ParticipantTotal  = count of number_bus

                 

                Why do all three summary field count the same field? the AM and PM fields should count the fields that have data if that record is for an absent participant. This assumes that the field has data if the participant is absent and is empty if the participant is not absent.

                • 5. Re: Sub-Summary with Calculations
                  icotta

                  I guess I misunderstood what I needed to do.  I now have::

                  PMAbsenceTotal  = count of code_absence_pm

                  AMAbsenceTotal  = count of code_absence_am

                  ParticipantTotal  = count of name_last

                   

                  I get a total for each bus, but it is the total number, not the number minus the records that have any absence codes.

                  For example on Bus A there at a total of 11 participants. Two of them have absence code  in both the am absence code and the pm absence codes. The number I need for that bus is 9.  It still shows the 11.

                   

                  I guess I don't understand how the summary fields work to retain the correct data when switch from finding absence codes in the am field to finding absence codes in the pm field

                   

                  I don't know that I am explaining myself very well.  Sorry!

                  • 6. Re: Sub-Summary with Calculations
                    icotta

                    I don't know what changed, but when I tried it again it workded!! I am so appreciative of you help with this.  You mademy (and my supervisor's) day!!!

                    • 7. Re: Sub-Summary with Calculations
                      philmodjunk

                      If you refer to a summary field in a calculation, you get a grand total--the total of all the records in your found set, unless you make correct use of GetSummary. The two parameters of GetSummary should be the summary field from which you need a subtotal and the "break" or "when sorted by" field you would use for a sub summary part.

                       

                      If your records are sorted by Number_Bus, then:

                      GetSummary ( ParticipantTotal ; number_bus )  should give you the total of all participants for a given bus

                      GetSummary  (AMAbsenceTotal ; number_bus ) should give you the total AM absent for a given bus

                      GetSummary ( PMAbenceTotal ; number_bus ) should give you the total PM absent for a given bus

                      • 8. Re: Sub-Summary with Calculations
                        icotta

                        Thank you!  That makes it much clearer for me.  One last question.  What would I do to get a trailing grand summary number that would be a total of my calculation field ParticipantDailyTotal you created for me.

                        • 9. Re: Sub-Summary with Calculations
                          philmodjunk

                          Define a summary field that totals this calculation field and put it in the trailing grand summary.

                          • 10. Re: Sub-Summary with Calculations
                            icotta

                            Thank you!  I had tried but used an incorrect field.  I must be getting tired  Of course yours worked!  I truly appreciate your help with all of this.  You are wonderful!

                            • 11. Re: Sub-Summary with Calculations
                              icotta

                              I am still trying to get this summary field to calculate correctly.  I created:

                              Summary filed: TotalAttending  = Count of ParticipantDailyTotal.

                               

                              ParticipantDailyTotal  is the calculation field you helped me with and it gives the correct totals:

                              ParticipantDailyTotal

                              Let ( p = GetSummary ( ParticipantTotal ; number_bus ) ;

                                     Max ( p - GetSummary  (AMAbsenceTotal ; number_bus );

                                               p - GetSummary ( PMAbenceTotal ; number_bus )

                                             ) // Max

                                    ) // Let

                               

                              The Summary field is counting the total number of participants on the buses (49) not the total of attending (40) that is the count of the ParticipantDailyTotal.

                               

                              I must be doing something wrong, but can't figure it out. Any ideas would be appreciated.

                              • 12. Re: Sub-Summary with Calculations
                                philmodjunk

                                On second thought, my previous suggestion isn't going to work.

                                 

                                You actually have an AM and a PM figure possible for a daily total of all that rode a bus. I'm going to assume that you want whichever count is largest.

                                 

                                A calculation field defined as:

                                Max ( ParticipantTotal - AMAbsenceTotal ; ParticipantTotal - PMAbsenceTotal )

                                 

                                Could be placed in the trailing grand summary to get whichever count is largest.

                                • 13. Re: Sub-Summary with Calculations
                                  icotta

                                  Thank you!!! That worked perfectly!!  I appreciate all of your help