8 Replies Latest reply on Apr 14, 2010 6:22 PM by imgaryshap

    Counting only unique values.

    jamie.hunt

      Title

      Counting only unique values.

      Post

      I hope someone can help me here, I'm sure there is an easy way to do this. I am looking to set up a calculation to only count unique values...

       

       

      For example if I have 5 records with the following ID's:

       

      ID1, ID2, ID2, ID3, ID4, ...the count will return 4 only counting each unique value.

       

       

       

       

      And if I have 10 records say with the ID's: 

       

      ID1, ID2, ID2, ID3, ID4, ID5, ID6, ID7, ID8, ID8   ...the count will return 8 counting only each unique value 

       

       

       

      I hope someone can help me this this,

       

      Many thanks. 

        • 2. Re: Counting only unique values.
          jamie.hunt
            

          Comment, After reading I'm still a little unsure. Can I Count the unique values in a calculation, or do I have to add more fields?

           

          Thanks 

          • 3. Re: Counting only unique values.
            philmodjunk
              

            Key question not answered in your posts:

             

            Is this the count of all unique values in the entire table, the current found set or some other group of records? The link comment posted describes more than one solution and the best fit will be determined by which of the above scenarios apply.

             

            If you want a count of all unique values in the entire table, I'd use the counting value list items method as this neatly fits into a single calculation field after you've defined the appropriate value list.

            • 4. Re: Counting only unique values.
              jamie.hunt
                

              Thank you for your reply. I am still fairly new to filemaker so bare with me...

               

              I have two separate filemaker files. One file contains all of my information, a table called Tickets, and one of the fields in the Tickets Table is "TicketID"  which contain values such as ID1, ID2, ID2, ID3, ID4 etc. Thats the field I am trying to count the unique vales of.

               

              My other File contains a Tracking Sheet. One table in this file is called TicketsCounted and is related to the Tickets table in the other file. I have a calculation which counts all values as long as they relate (Have the same date) however am now stuck as to how to count just the unique values (which have the same date)

               

               

              I would appreciate some more information on this... Like I said I am still fairly new to Filemaker.

               

              thanks 

              • 5. Re: Counting only unique values.
                philmodjunk
                  

                Hmmm, comment may suggest a better approach, but I'm thinking of setting up a conditional value list and counting the values. Such a value list will list one instance of each value so it'll filter out the duplicates so we can get our unique count of related items.

                 

                Use Manage | Value Lists to create a new value list called "Tickets".

                In the Edit Value List dialog, click the Specify Field... button

                In the Use values from first field column, select "Tickets" from the drop down and Click the TicketID field to highlight it.

                Click the Include only related values starting from... radio button and select Tracking Sheet as the "Starting From" table.

                Click OK repeatedly to dismiss the dialogs.

                 

                Open Manage | Database | Fields and create a new field of type calculation called cTicketCount in the Tracking Sheet table.

                Enter this as the calculation expression:

                 

                ValueCount ( ValueListItems ( Get (filename) ; "Tickets" ) )

                 

                Click the storage options button and make this an Unstored calculation to make sure it updates as records are created/deleted in the Tickets table.

                • 6. Re: Counting only unique values.
                  jamie.hunt
                     Do I need to create the value list in the file which holds the tickets, or the file which holds the tracker?
                  • 7. Re: Counting only unique values.
                    jamie.hunt
                       Figured it out... Thank you so much for all your help!
                    • 8. Re: Counting only unique values.
                      imgaryshap

                      I'm using FMP 8 on a MAC.

                       

                      I tired both these solutions.  The latter - creating a value set - works for a full database, but doesn't seem to work when pulling a specific report.

                       

                      the other solution works if there are no sub-categories. 

                       

                      for example, sticking with "comments" solution to the IRS example, those three field work great when running a single First Quarter Report.  in one quarter there are 11 records and only 2 employees, thus creating those three new fields does generate a solution that show there were only 2 employees.

                       

                      But what if you need a quarterly report by year?

                      2010 - 1Q

                      A

                      B

                      C

                       

                      2Q

                      A

                      B

                      D

                      E

                      F

                       

                      3Q

                      B

                      C

                      F

                      G

                       

                      etc...

                       

                      what break field do you use, because when you sort by quarter and use employee as a break field, it doesn't work - the result is "4?"