6 Replies Latest reply on Aug 15, 2014 6:37 AM by PaulaS

    Need a summary field not to count duplicates

    kilbot

      Title

      Need a summary field not to count duplicates

      Post

      I have a summary field to count the number of assigned investigators to cases but I do not want it to count every name just each different name once. Is there a way to make the summary field not count duplicates?

       

      or is there a way to have my grad summary field count how many sun summaries I have because my subs are sorted out by investigator?

        • 1. Re: Need a summary field not to count duplicates
          philmodjunk

          Here's a trick I learned from a post by Comment:

           

          Start with a basic "Count of" summary field, TotalCount, and specify it to count a field that is never blank such as an Investigator ID field

           

          Define a calculation field, cFraction as

           

          1/ Get Summary ( TotalCount ; Investigator ID )

           

          (Investigator ID is your "break field". Make sure you sort your records on this field in order to get correct results.)

           

          Define a second summary field, UniqueCount and define it as the total of cFraction.

           

          Use this second summary field to report the number of investigators.

          • 2. Re: Need a summary field not to count duplicates
            PaulaS

            I am doing something similar and have hit a mental roadblock. I have the calculation field (1/GetSummary, etc) but it is showing no value on my layout while in the Data Viewer it is showing the correct calc. I know the Data Viewer has a slightly different calc that is pointing to the table occurrence while my calc field is pointing directly at the table. Would this cause the problem?

            • 3. Re: Need a summary field not to count duplicates
              philmodjunk

              This thread assumes that all fields are part of the same table and that the layout where the report is set up refers to an occurrence of that same table in Show Records From in Layout setup.

              The found set of records must be sorted in a sort order that includes the "break" field specified in the get summary function.

              BTW, this thread no longer appears in Recent Items when you post a reply to it. I almost missed it due to that reason. I believe that any threads where the initial post is more than 3 months old will no longer appear in Recent Items when new replies are posted to it.

              • 4. Re: Need a summary field not to count duplicates
                MichaelVickers

                Just want to thank you for this thread. I used your 1/(GetSummary....) to count unique summary items and it worked perfectly.

                :)

                • 5. Re: Need a summary field not to count duplicates
                  jmvatc

                       How would this work with two sub summaries?

                       I have a similar situation, but (in trying to keep with this example) I have a report that needs to show "cases" and "elements ". I am looking to show a count of "investigators" at the cases sub-summary and "elements" sub-summary levels. Do I need to repeat the above process for each sub-summary, and be sure to have the field set to restart at that level?

                       BTW, this method results in "1".

                  • 6. Re: Need a summary field not to count duplicates
                    PaulaS

                         I think this is a viable solution but I didn't get around to writing it and lo and behold my users no longer have a problem.

                         Currently I'm chalking it up to our IS dept. working with our servers during the move to a new location - I have no answers and as long as it's working I'm just going to let this go.

                         Good luck to you jmvatc. :)

                         Thanks PhilModJunk.