1 2 Previous Next 15 Replies Latest reply on Mar 28, 2014 12:01 PM by sccardais

    Sub Summary based on Subset of Parent Records

    sccardais

      Title

      Sub Summary based on Subset of Parent Records

      Post

           The partial screenshot below is from a layout (Ranking of Issues) showing records from Join_Issues_Assigned which is related to Surveys.

           The calculation in columns 2 (Count) and 4 (% of Surveys...) are incorrect.

           This report was created by searching Surveys containing a value of "1" in the field "PrimeMarkets" . 310 records out of 540 meet this criteria.

           In that subset of 310 records, 90 surveys have been tagged with "Complex" in the field Issues Mentioned" in table Join_Issues_Mentioned.

           The calculation in column 4 should show 29.03% (90/310)

           Instead, it is showing 32.26% - the result of dividing all of the records with Complex in field "Issues Mentioned" by 310 - the correct subset. 

           Column 2 (Count) is a Summary field, based on a calculation field Count > Issues Mentioned in table Join_Issues_Mentioned.

           So - what do I need to do to get the Count field to calculate each of the issues on the subset of records found in Surveys?

           Do I need a new table occurrence and if so, what would that relationship look like?

      Prime_Account.jpg

        • 1. Re: Sub Summary based on Subset of Parent Records
          philmodjunk

               As I recall, each row shown here is a sub summary layout part when sorted by the value in column 1 (Join_Issues_Mentioned)?

               I don't understand this part:

               

                    Instead, it is showing 32.26% - the result of dividing all of the records with Complex in field "Issues Mentioned" by 310 - the correct subset. 

               "all of the records with Complex in field "Issues Mentioned" " is all of the records in your found set or all of the records in the table? Are there 90 records with "complex" in your found set or 100?

               From what I see, I'd be inclined to temporarily add a body to this layout and check to see what actual records are being pulled into your found set for your report. If I understand what I am seeing correctly, you don't have the records in the found set that you expected to get and thus your numbers are also not what you expected.

          • 2. Re: Sub Summary based on Subset of Parent Records
            sccardais

                 There are a total of 860 records in Surveys.

                 310 of these have a "1" in field "PrimeMarkets" AND have at least one Issue Mentioned in Join_Issues_Assigned. As you can see from the screenshot, 

                 This found count is being stored in a global variable. You can see the value of 310 marked with the red arrow. The column showing 32.26% is a calculation field that uses the global variable. GetSummary(Summary_Count_Issues;Issues Mentioned)/$$SurveysWithIssues

                 When I do a manual search of Surveys to find those with "1" in PrimeMarkets AND "Complex" in Issues Mentioned, 90 records (in Surveys) are found.

                 So, the report should show 90 records for Complex and the 32.26% should be 90 / 310 (29.03%)

                 Thanks for your help.

            • 3. Re: Sub Summary based on Subset of Parent Records
              philmodjunk

                   But you show a count of 100 and that would appear to be the issue here. 100/310 = 32.26%.

                   Are you sure that your count column is counting records in Surveys and not records in Join_Issues_Assigned? Could you have 100 records in Join_Issues_Assigned linked to your 90 Survey records?

                   (And apologies if this is already understood by you, but I am just now parsing out the key details.)

              • 4. Re: Sub Summary based on Subset of Parent Records
                sccardais

                     Yes. You're correct. The Count should be (I want it to be!) 90 because that is the number of Surveys with "1" in PrimeMarket and with at least one related recored with Complex in Issues Mentioned.

                     The value is the Count column is a Summary field in Join_Issues_Mentioned that summarizes a calculation field in Join_Issue_mentioned that is a count of another field (Issues Mentioned) in Join_Issues_Mentioned.

                     So - in answer to your question, yes. There ARE 100 records in Join_Issues_Mentioned with Complex in the Issues Mentioned field but I was hoping (!) and wanting the Count calculation to adjust for the found count of records in Surveys.

                     Make sense?

                • 5. Re: Sub Summary based on Subset of Parent Records
                  philmodjunk

                       Just to better understand things, what this tells me is that Survey A, might have two records in Join_Issues_Assigned both linked to the same record in Issue?

                       Why would that happen? What does that accomplish for you?

                       I ask that because if we could limit the join table to records that only link a given survey record to a given survey a single time, then the count of records for a given Issue in the join table will match the number of survey records and we'll get 90 records here instead of 100.

                       Otherwise, were up against a "unique values" problem and while there are several solutions--one uses SQL and the other uses very interesting sorting, summary and getsummary function based calculations, it would be much simpler if we didn't have what appears to me to be duplicate records in your Join table.

                       Unique counts with ExecuteSQL: A new way to count unique values in FileMaker 12

                       Unique counts using summary fields and GetSummary(): How to count the number of unique occurences in field.

                  • 6. Re: Sub Summary based on Subset of Parent Records
                    sccardais

                         I'm probably answering your questions incorrectly.

                         Survey A should not have duplicate Issues in Join_Issues_Mentioned.

                         Surveys has a total of 860 records. 781 of these have "1" in field PrimeMarket. 310 Surveys have one or more Issues Mentioned in Join_Issues_Mentioned. 90 have "1" in PrimeMarkets AND Complex in Join_Issues_Mentioned.

                         Does this make more sense? It's giving me a headache!

                          

                    • 7. Re: Sub Summary based on Subset of Parent Records
                      philmodjunk

                           Then the number of "complex" join records pulled up for your report should be the same as the number of Survey Records that are linked to a "complex" issue and that have 1 in PrimeMarket. There shouldn't be 100 complex records in your found set of join table records. You need to look and see why there is an extra 10 records linked to the "complex" issues record in the join table.

                           I would guess that either you do have duplicates that shouldn't be there or something didn't work right when pulling up a found set of records in your join table for this report.

                           What should work, is to go to the join table layout, enter find mode and specify a 1 in Surveys::PrimeMarket and perform the find. This will only find join table records that have at least one related record in Surveys with 1 in the primeMarket field.

                      • 8. Re: Sub Summary based on Subset of Parent Records
                        sccardais

                             These numbers are slightly different than above because I added about 112 records since last post.

                             The Ranking of Issues layout is based on Join_Issues_Mentioned.

                             I want to show the % each Issue represents on different found sets in Surveys.

                             One example of a found set in Surveys records containing 1 in PrimeMarkets.

                             I want to find all of the records in Surveys that have 1 in PrimeMarket AND that have at least related record in Join_Issues_Assigned, As of this minute, that number is 334.

                             I want to divide the number of records in Surveys that have 1 in Prime Markets AND Complex in Issues assigned (currently 95) by the number of records in Surveys with 1 in PrimeMarket AND with at least one related record in Join_Issues_Assigned. (currently 334)

                             The percentage should be 28.44%

                             I think the problem is with the global variable that's part of the calculation formula used to do this calculation. The calc field is Percent of Surveys. Here's the formula.  GetSummary(Summary_Count_Issues;Issues Mentioned)/$$SurveysWithIssues.

                             Maybe the right question is "How can I capture the found set from surveys and use it in this calculation so that it updates properly?

                              

                             Thanks again for all of your help. 

                        • 9. Re: Sub Summary based on Subset of Parent Records
                          philmodjunk

                               From here, it still comes down to having the wrong number of records in your Join table.

                               That then throws off your counts used in the calculation.

                          • 10. Re: Sub Summary based on Subset of Parent Records
                            sccardais

                                 You may be right but I still don't see why you are so sure there's an error in the Join table.

                                 Don't get me wrong, if there's an error, I want to know but I don't see it. maybe the attached will help.

                                 After seeing this, do you still feel there must be an error in the Join table itself or  - hopefully - in the calculation and handling of the Global variable? Can you suggest a way to test either Surveys or Join for the errors you suspect?. 

                                  

                            • 11. Re: Sub Summary based on Subset of Parent Records
                              philmodjunk

                                   There's an "error" because the number of records doesn't match. This may not be an error in the join table, however, it depends on whether your design really works to not have two join records linked to both the same survey and the same issue. (Note: You could check for the possible existence of such duplicates very easily by adding a calculation field that combines the two ID values in a single field and then using the ! operator in a find.)

                                   But it's not the total number of join table records that's the problem here, it's the specific number of records linked to a specific issue record that your earlier screen shot shows to be more than the number of Survey records that you've indicated link to those records. You indicated that you had 90 records in your found set that were linked to the "complex" issue. But your screen shot of records in the Join table show a count of 100 join table records linked to the "complex" issue record. Therefore, you have records in the join table that should not be there.

                              • 12. Re: Sub Summary based on Subset of Parent Records
                                sccardais

                                     OK. Here's my relationship graph. To do the test you suggested, what fields do I concatenate and in which table should I create the field in?

                                • 13. Re: Sub Summary based on Subset of Parent Records
                                  philmodjunk

                                       In Join_Isssues_Assigned, define a calculation field such as:

                                       Issue_IDfk & " " & Survey ID_fk

                                       put that field on a Join_Isssues_Assigned layout, enter find mode and put a ! into this field.

                                       If any records are found, you have duplicates.

                                       But please note that this only one of two ways that you can end up with the wrong number of records in the join table.

                                  • 14. Re: Sub Summary based on Subset of Parent Records
                                    sccardais

                                         Ok.

                                         Did that and nothing found.

                                         You mentioned a second way to test for error.

                                    1 2 Previous Next