
1. Re: SubSummary with Calculations
philmodjunk Mar 10, 2017 10:59 AM (in response to icotta)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: SubSummary with Calculations
icotta Mar 10, 2017 11:05 AM (in response to philmodjunk)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: SubSummary with Calculations
icotta Mar 10, 2017 1:03 PM (in response to 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: SubSummary with Calculations
philmodjunk Mar 10, 2017 1:19 PM (in response to icotta)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: SubSummary with Calculations
icotta Mar 10, 2017 2:01 PM (in response to philmodjunk)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: SubSummary with Calculations
icotta Mar 10, 2017 2:12 PM (in response to 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: SubSummary with Calculations
philmodjunk Mar 10, 2017 2:13 PM (in response to icotta)If you refer to a summary field in a calculation, you get a grand totalthe 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: SubSummary with Calculations
icotta Mar 10, 2017 2:38 PM (in response to 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: SubSummary with Calculations
philmodjunk Mar 10, 2017 3:25 PM (in response to icotta)Define a summary field that totals this calculation field and put it in the trailing grand summary.

10. Re: SubSummary with Calculations
icotta Mar 10, 2017 3:44 PM (in response to philmodjunk)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: SubSummary with Calculations
icotta Mar 15, 2017 10:21 AM (in response to philmodjunk)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: SubSummary with Calculations
philmodjunk Mar 15, 2017 10:37 AM (in response to icotta)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: SubSummary with Calculations
icotta Mar 15, 2017 12:43 PM (in response to philmodjunk)Thank you!!! That worked perfectly!! I appreciate all of your help