3 Replies Latest reply on Jan 3, 2019 1:33 PM by Ken Flerlage

    Calculated Field To Omit A Table Row

    Greg Reinecke

      Hello,

      I have a workbook where an individual could belong to zero out of five clubs, belong to one club only or belong to multiple clubs.

      If that member belongs to zero clubs I still want to display that member entry and email address once. If the member belongs to multiple clubs I want to show name and email once but list ALL clubs. See below:

      OmitSelectRows.jpg

      I have attached a workbook. Thanks in advance and Happy New Year.

        • 1. Re: Calculated Field To Omit A Table Row
          Peter Fakan

          Hi Greg,

           

          You didn't attach a workbook. My initial thoughts are this could possibly be done with the Analysis > Aggregate Measures feature, however the reason why the blank rows are being generated is due to the email field, not the club name field. What do you want to do with the email addresses ?

           

          HTH

           

          Peter

          • 2. Re: Calculated Field To Omit A Table Row
            Greg Reinecke

            My apologies. It is now attached. I found if I slide email on the column shelf to the left between "First Name" and "Club Name"it makes things a bit better. Ultimately I want to always show member and email and club name(s). If they belong to zero clubs show Club Name as blank only in that single instance. Thanks.

            • 3. Re: Calculated Field To Omit A Table Row
              Ken Flerlage

              Create a calculated field that counts the number of clubs (using an LOD) for each person:

               

              Club Count

              // Count the number of clubs.

              {FIXED [Last Name], [First Name]: COUNTD(IIF([Club Name]<>"None", [Club Name], NULL))}

               

              Then create another calculated field as follows:

               

              Include

              // Filter out "None" for people with 1+ clubs.

              IF [Club Count]=0 THEN

                  "Include"

              ELSE

                  IF [Club Name]="None" THEN

                      "Exclude"

                  ELSE

                      "Include"

                  END

              END

               

              Then drag that field to filters and only include values of "Include".

               

              See attached workbook.