1 2 Previous Next 16 Replies Latest reply on May 21, 2010 9:57 AM by DRJAustin

    Countif equivalent (count matching text in a particular field)

    trialuser1111

      Title

      Countif equivalent (count matching text in a particular field)

      Post

      I have been looking on this forum and others and I have yet to find a solution that addresses my problem.  I want to be able to count the number of records that have a particular text value in a particular field.  Here's the situation:

       

      One of my layouts is a map, and it's based on my "Company Data" table.  Clicking on a city will (or will eventually) show a summary of the companies in that area.  So far, I have a very basic splash page that shows the total count of companies, as well as the top 5 companies as ranked by size.  However, I would like to add the count of companies by "style" (a field within the Company Data table).  Each company is assigned one of 10 or so styles.  I have tried various combinations of valuecount formulas and global calculations, but I can't seem to find anything that works.  This would be an exact replication of the =COUNTIF formula in Excel, if that helps.

        • 1. Re: Countif equivalent (count matching text in a particular field)
          comment_1
            

          Filemaker has no CountIf function (though you could write a custom function to emulate the effect). However, in this case you could use a simple alternative =

           

          ValueCount ( FilterValues ( List ( Companies::Style ) ; "SomeStyle" ) )

          • 3. Re: Countif equivalent (count matching text in a particular field)
            trialuser1111
              

            Using this formula returns a 0 or 1 based on whether or not the record matches the chosen style that I write in the formula.  I can combine this with a summary field and get the results I want, but that requires 20 new fields and seems a little roundabout.  I get an error message when I try to wrap the ValueCount function in a Sum function.  Just making sure I'm using the function correctly.

             

            Thanks for the help, at least I've got the right numbers now. 

            • 4. Re: Countif equivalent (count matching text in a particular field)
              trialuser1111
                
              I have tried this, and I end up with a total count of the companies in that region.  Does it matter where I place the field in my layout?  I'm looking to get all my subtotals in the header if possible, but that's not a must.  

              KIDO wrote:
              <!--             @page { margin: 2cm }          P { margin-bottom: 0.21cm }          A:link { so-language: zxx }        -->

              Have you explored a Sumary field capabilities?


              Define it as


              The Count of – your field -Running Count, checked – Restart summary for each sorted group, checked - When sorted by, your sort field style.


              You may need to keep a table with company styles to use it as the sort table.

               

              Regards

               


               


              • 5. Re: Countif equivalent (count matching text in a particular field)
                KIDO
                  

                trialuser1111 wrote

                 

                ________________________________________________________________________________________________

                 

                I have tried this, and I end up with a total count of the companies in that region.  Does it matter where I place the field in my layout?  I'm looking to get all my subtotals in the header if possible, but that's not a must.  

                _________________________________________________________________________________________________

                 

                You can place the field anywhere in your layout. You can use the Menu /Layouts /Set tabs order..., for navigation purpose.

                I don't hold suitable information of your database structure to figure out why you end up with a total count of the companies in that region. Depending on your database structure, you may or may not need to isolate the desirable data in a foundset, but feel free to provide further details if you need further assistance.

                 

                Regards 

                • 6. Re: Countif equivalent (count matching text in a particular field)
                  comment_1
                    

                  trialuser1111 wrote:

                  Using this formula returns a 0 or 1 based on whether or not the record matches the chosen style


                  I am sorry - I missed the fact that your layout is based on the Companies table, while my formula is intended to work over a relationship.

                  The truth is that there are quite a few ways to do this. The simplest one is to show a summary "report", sub-summarized by Style (which in version 10 can be viewed in Browse mode). However, you won't be able to show this AND "the top 5 companies as ranked by size" at the same time - because each of these require a different sort order.

                  So you could either use a custom function (if you have the Advanced version), or define a relationship to use the method I have suggested earlier. The relationship could be simply a self-join using the x relational operator (thus making all records related). Then you can summarize each style over the relationship as =

                  ValueCount ( FilterValues ( List ( Companies 2::Style ) ; "SomeStyle" ) )


                  To avoid having to use 10 or so fields, you could present the result as a text block, e.g.:

                  List (
                  "Style 1" & Char (9) & ValueCount ( FilterValues ( List ( Companies 2::Style ) ; "Style 1" ) ) ;
                  "Style 2" & Char (9) & ValueCount ( FilterValues ( List ( Companies 2::Style ) ; "Style 2" ) ) ;
                  "Style 3" & Char (9) & ValueCount ( FilterValues ( List ( Companies 2::Style ) ; "Style 3" ) ) ;
                  ...
                  )


                  or use a repeating calculation field.


                  There is also a way to show the summary in a portal, but I'm afraid it's too complex to explain within the confines of a forum message (with no option to atach a file).










                  • 7. Re: Countif equivalent (count matching text in a particular field)
                    trialuser1111
                      

                    I'm afraid I need a little more explanation on how this function would work.  I DO have a layout that uses the sub-summary when sorted by style (and various other characteristics), but I am trying to prepare a graphical summary as well, so I need those counts to be available to me in other functions.  What I have done so far is right three functions for each style or characteristic I am looking to tally: (1) the valuecount function to obtain the 0/1 result, (2) a summary field to total the valuecount fields, and (3) a substitute function that converts the sum into a simple bar graph where "|" represents the % of the total.

                     

                    While somewhat tedious, this has worked for company style, company region, and a couple other categories because those are all based on finite criteria (we only define 10 styles and 7 regions).  However, I would now like to create a similar summary graph by sponsor, and the sponsor list is not finite.  There are about 25 COMMON sponsors but it technically could be anything, so I don't know what to put in the valuecount function after the semicolon, i.e.:

                     

                    ValueCount ( FilterValues ( List ( Meetings::Sponsor) ; ???? )

                     

                    If it helps, here's a little background on my database structure: I am looking to summarize meetings (recorded in the Meetings table), sometimes by criteria based on the company at the meeting (companies are maintained in the Companies table, which is related to Meetings by a CompanyID field), and sometimes by criteria of the meeting itself.  

                    • 8. Re: Countif equivalent (count matching text in a particular field)
                      comment_1
                        

                      As you are discovering, this is easy to do in a summarized report, but not so easy otherwise - esp. when the number of categories is not known beforehand.

                      One option here would be to define a value list of the categories, and a custom function that iterates over the list items. 

                       

                       

                      Another way to "de-dupe" a portal is the so-called 'Ugo method', which I will try to describe briefly:

                      1. Define a self-join relationship of the child table as:

                      Child::Category = Child 2::Category


                      2. Define a calculation field cUniqueID in the child table =

                      Case ( ChildID = Child 2::ChildID ; ChildID )


                      3. Define another calculation field cCountCategory =

                      Count ( Child 2::ChildID )


                      4. Define another self-join relationship of the child table as:

                      Child::cUniqueID = Child 3::ChildID


                      A portal from the parent table into Child 3 will show one record per category, and the calculation field cCountCategory will show the record count in each category.










                      • 9. Re: Countif equivalent (count matching text in a particular field)
                        aikiko

                        Hi

                         

                        I'm very new to FMP10 (1 month) so please bare with me. This query has come up on numerous forums and I'm sure they are very simple to answer, however despite reading and using other people's ideas on various forums I still cannot get it to work on my DB.

                         

                        Problem 1: I want to count the number of times a test result (in text) appears in my database with 40,000 records. The table is called 'Microbiology Results', the field is called 'Culture results' and the value list is called 'results' with 3 possible values to choose from i.e. 'MTB', 'NTM' and 'Negative'

                         

                        I tried using the following to create a calc field to count the number of 'Negative' results but to no avail: 

                         

                        ValueCount ( FilterValues ( List ( Microbiology Results::Culture results ) ; "Negative" ) )

                         

                        I can (sort of ) get around this by creating another calc field which converts Negative into a 1, then count the number of 1s, however I will end up with numerous extra calculation fields (in the future) making FMP unwieldy to use. 

                         

                        Problem 2: How can I count the number of times a test result appears if someone is 'Male' or 'Female' or 'Unknown' (field name is 'gender')

                         

                        e.g. How many 'males' have a 'negative' result? How many 'females' have an 'MTB' result?

                         

                        Hopefully I don't have to resort to using Excel to do all of this and thank you very much for anyone who can help me on this one.

                         

                        Cheers

                        Aikiko

                         

                        FMP10(Adv); OSX 10.6

                         

                         

                         

                        • 10. Re: Countif equivalent (count matching text in a particular field)
                          comment_1

                          Define a summary field as Count of CultureResults (or any field that cannot be empty, such as serial ID).

                           

                          Add a sub-summary by CultureResults part to your report layout, and place the CultureResults field and the summary field there. Delete the body part of the layout.

                           

                          When you sort the records by CultureResults, you should see something like:

                          • MTB 15,456

                          • NTM 18,195

                          • Negative 9,734

                           

                          To add a further breakdown by Gender, add another sub-summary part (byGender), and sort the records by CultureResults and by Gender. The same summary field can be used for both.

                          • 11. Re: Countif equivalent (count matching text in a particular field)
                            aikiko

                            Dear comment,

                             

                            Thank you very much for your assistance.

                             

                            It's taken me three days ('Oh dear' - I hear everyone say) to figure out how to set up a subsummary report  and I still cannot get it to work properly(!).

                             

                            Alas, although a subsummary will probably give me what I need, I still need to figure this out for as many as 400 subsummaries and cross-tabulations. For example,

                             

                            How many people have test result X?

                            How many people between the age of 15-24 have test result X?

                            How many males between the age of 15-24 have test result X?

                            How many males between the age of 15-24 have test result X and have had a chest x-ray?

                            etc etc etc

                             

                            As such, I have drawn multiple RxC tables in other layouts (with 400+ boxes), and I have (naively) figured that if I can generate one calculation, then I can create 400 calculations which I can drag into each of these 400+ boxes, such as (or similar to) the one I noted previously:

                             

                            valuecount (filtercount (list (microbiology results::culture results;"Negative"))

                             

                            So my question is: 

                             

                            How can I create a calculation that can determine the number of people who have a certain 'Culture Result' (text field with 3 possibilities: MTB, NTM, Negative') by asking:

                             

                            1) How many people have MTB?

                            2) How many people have NTM?

                            3) How many people have Negative?

                             

                            then

                             

                            4) How many people have MTB and are male?

                            5) How many people have NTM and are male?

                            6) How many people have Negative and are male?

                             

                            ..etc...

                             

                            399)...

                            400)...

                             

                            (I have numerous other variables to sort by which include age, gender, chest xray, drugs taken, smoker, alcohol etc etc)

                             

                            The aim is that if I can get FMP to dynamically update these RxC tables as our survey progresses over the course of the study, so that I can print out progress reports without having to determine these 400+ questions on a monthly basis.

                             

                            Once again, I hope I'm going down the right path, and sincerely appreciate any assistance you or anyone else can offer.

                             

                            Cheers

                            Aikiko

                             

                            FMP10(Adv); OSX 10.6

                             

                             

                             

                             

                             

                             

                             

                             

                             

                            • 12. Re: Countif equivalent (count matching text in a particular field)
                              comment_1

                              I'm having a problem with the "etc...." part. Let me try and make a couple of general points instead:

                              1. Filemaker does not do crosstabs, at least not natively. Contrary to a spreadsheet, Filemaker is not flexible in the horizontal direction. The number of columns shown on a layout is fixed in advance when you design the layout.


                              2. A layout can have as many sub-summary parts as you want. You can choose which sub-summaries will actually appear in a report by including their breakfield in the sort order.

                              However, the order of the sub-summaries cannot be changed dynamically (you will need another layout for that), and neither can the breakfield chosen for a specific sub-summary part.



                              • 13. Re: Countif equivalent (count matching text in a particular field)
                                aikiko

                                Dear Comment,

                                 

                                Thank you very much for your thoughts. The past few weeks have been a steep learning curve indeed. 

                                 

                                However, my problem(s) still persist, and after scouring the forums, I believe you hinted at what I am trying to do:

                                 

                                http://forum-en.filemaker.com/t5/Using-FileMaker-Pro/Help-with-calculation-Sum-of-a-found-set-without-performing-the/m-p/53028/highlight/true#M41568

                                 

                                In your first reply, you mention:

                                 

                                Alternatively, you could use a "grid" table of 9 records (3 locations x 3 stages) and summarize over a relationship to the Cabinets table. 

                                 

                                What is this 'grid' table you are referring to?

                                 

                                I am thinking a 'grid' table is as follows: I have drawn a table in a new layout (column headings being MTB, NTM, Not Known, row headings being Male, Female, Unknown), then created some calc fields and placed them into the table. However I am having trouble creating calculations for those fields that require 'conditional' (I think I have used the word correctly here) counting. For example, I am trying to determine the number of 'Males' AND those who have the disease 'MTB', and then the number of 'Females' AND those who have 'MTB' etc. 

                                 

                                Many thanks for your time once again, and any thoughts are much appreciated.

                                 

                                Aikiko

                                 

                                 

                                 

                                 

                                • 14. Re: Countif equivalent (count matching text in a particular field)
                                  comment_1

                                   


                                  aikiko wrote:

                                  I have drawn a table in a new layout (column headings being MTB, NTM, Not Known, row headings being Male, Female, Unknown),

                                  ...

                                  For example, I am trying to determine the number of 'Males' AND those who have the disease 'MTB', and then the number of 'Females' AND those who have 'MTB' etc.


                                   

                                   

                                  I am afraid you are thinking more of a spreaddsheet than a database. There are no row headings in Filemaker, only records - and all records in a table have the same columns.

                                   

                                  In your example, the "grid" table would have two columns: Disease and Gender, and there would be a record for each possible combination of these two (3x3= 9 records in this case). The relationship to your data table would match on both fields, so that each record in the "grid" table sees only the relevant data records.


                                  1 2 Previous Next