1 2 Previous Next 16 Replies Latest reply on Jul 17, 2017 12:26 PM by ljungholms

    Need help on a calculation

    ljungholms

      I am looking to create a series of calculations using IF statements. I have 2 field in my related table. Round # and Pass/Fail.

      I need to calculate the pass rate as a percentage for RD1, RD2 etc.

       

      So

      if round 1, summary of field/total round 1 records

      if round 2, summary of field/total round 2 records

       

      Kind of stuck on this one.

       

      Thanks

        • 1. Re: Need help on a calculation
          ljungholms

          By the way, this is what I currently have.

           

          Status Flag   -- 1 is pass 0 is fail

          Status Flag Summary - Summary of Status Flag

          QC Round Count - Count (QC Data::Status Flag Number )


          Pass percentage

          QC Data::Status Flag Summary/QC Round Count

           

          This works as a start. Now I need to figure out how to only make it work if Round=1, then another calc if Round=2

          • 2. Re: Need help on a calculation
            ljungholms

            Anyone have any ideas??

            • 3. Re: Need help on a calculation
              philipHPG

              There are a couple of different options I can think of here:

               

              1). Create additional table occurrences where the relationships are hard-coded to round 1 and round 2 (gets a little convoluted if you have a variable number of rounds) and then base the calculation on those table occurrences

              2). Create additional table occurrences but instead of hard-coding the relationships, use a single-line filtered portal to show a summary field (again this becomes convoluted if you have a variable number of rounds)

              3). Use ExecuteSQL

              4). Change your layout structure so that you can use subsummary parts sorted by Round. (The simplest solution, but may not meet your UI requirements)

              • 4. Re: Need help on a calculation
                brianb

                I am looking to create a series of calculations using IF statements. I have 2 field in my related table. Round # and Pass/Fail.

                I need to calculate the pass rate as a percentage for RD1, RD2 etc.

                 

                So

                if round 1, summary of field/total round 1 records

                if round 2, summary of field/total round 2 records

                 

                Kind of stuck on this one.

                 

                Thanks

                Instead of If statements  the case function may simplify the calculation

                 

                case (

                 

                Field1 = X; *Do this calculation";

                Field1 = Y; "Do a different calc";

                 

                )

                 

                You can then expand the arguments and if needed use intermediate calc fields

                • 5. Re: Need help on a calculation
                  ljungholms

                  Thanks. I still can't get it working. I have a feeling it is due to the summary field.

                   

                  What I really need is a summary of a field if Round=1

                  Another summary if Round=2

                  • 6. Re: Need help on a calculation
                    brianb

                    when you say summary of field?   do you mean the number of "pass"   counted when roundfield = 1?

                    • 7. Re: Need help on a calculation
                      philipHPG

                      If you really want to go that route, you can create additional calc fields in your related table:

                       

                      Round1Count: If ( Round = 1 ; Status Flag ; 0 )

                      Round2Count: If ( Round = 2 ; Status Flag ; 0 )

                       

                      and then summary fields of each of those. Again this becomes convoluted if you have a lot of rounds.

                      • 8. Re: Need help on a calculation
                        ljungholms

                        Yes, the rounds do vary, usually not more than 7. I just hate creating that many fields when a calculation might work. May be the OCD in me.....

                        • 9. Re: Need help on a calculation
                          philipHPG

                          I agree that creating extra fields for that is not the best approach.

                           

                          Using one of the other options I mentioned is probably a better approach.

                           

                          If you can restructure your layout to use a list view with sub-summary part that would probably be the simplest.

                           

                          Otherwise, ExecuteSQL is a good option, but, if that is overwhelming, using additional table occurrences (either hard-coded or through a single-line filtered portal) would also work.

                          • 10. Re: Need help on a calculation
                            TSGal

                            ljungholms:

                             

                            Thank you for your post.

                             

                            Since "Status Flag" has a value 100% (1) or 0% (0), set your Summary field to be an Average of Status Flag.

                             

                            Find all records for Round 1, and if there are 5 records, 2 passing, you will get an average of .4 or 40%.

                             

                            Set up a Sub-summary by Round, sort by Round, and include the Summary field in the Sub-summary part.

                             

                            TSGal

                            FileMaker, Inc.

                            • 11. Re: Need help on a calculation
                              ljungholms

                              Thanks for the help everyone. I am attaching a dummy file I created to show everyone what I am trying to do.

                              • 12. Re: Need help on a calculation
                                philmodjunk

                                Can't look at your file with my smart phone and my wife will shoot me if I dig out a computer while on vacation, but you might need the getSummary function if you need sub totals of the type you see in subsummary parts.

                                • 13. Re: Need help on a calculation
                                  jfletch

                                  Phil! Shut off your email. Some may not like to hear it, but the Community will still be here when you return. There's plenty of time to solve all the FileMaker world's problems AFTER you've had a good relaxing vacation. Just do it.

                                  1 of 1 people found this helpful
                                  • 14. Re: Need help on a calculation
                                    TSGal

                                    ljungholms:

                                     

                                    Enclosed is your updated file.  I used Average Summary fields to get the values you wanted for each Round.  I left your original fields in place.

                                     

                                    TSGal

                                    FileMaker, Inc.

                                    1 2 Previous Next