2 Replies Latest reply on Nov 3, 2016 11:18 PM by Norbert Maijoor

    Grouping based on which columns are populated.

    william.boyd.0

      Hello,

       

      I have a list of people, along with their Home/Cell/Business phone numbers, as follows in this example (I can't seem to type after the table, so the table example will be at the bottom of my post.)

       

      I want a graph that shows:

           People with no phone numbers: 2

           People with all phone numbers: 1

           People with only home phone numbers: 0

           People with only cell phone numbers: 0

           People with only business phone numbers: 0

           People with only a home phone and cell phone: 1

           People with only a cell phone and business phone: 2

           People with only a home phone and business phone: 0

       

      I basically want to be able to see a count of non-null fields for every phone combination.

       

      Thank you.

       

      Client IDClient NameHome PhoneCell PhoneBusiness Phone
      1John555123456755598734245553426443
      2JaneNULLNULLNULL
      3DerekNULL55578442355557364786
      4BillNULLNULLNULL
      5

      Sarah

      NULL55543334215553423631
      6Mark55556423425553423462NULL
        • 1. Re: Grouping based on which columns are populated.
          Andrée Roos

          Hi.

          Here is one way of doing it:

           

          1. Create one new field for each phone type:

          PhoneCombinations1.png

          2. Concatenate them like this:

           

          PhoneCombinations2.png

          3. Result:

          PhoneCombinations3.png

          • 2. Re: Grouping based on which columns are populated.
            Norbert Maijoor

            Hi William,

             

            Find my approach as reference below and stored in attached workbook version 9.3

             

             

             

             

             

            Calculatore:

            If    [COUNTD([Business Phone]])]+      [COUNTD([Cell Phone]])]+      [COUNTD([Home Phone]])]=0 then 2

            elseif [COUNTD([Business Phone]])]+      [COUNTD([Cell Phone]])]+      [COUNTD([Home Phone]])]=3 then 1

            elseif [COUNTD([Business Phone]])]=0 and [COUNTD([Cell Phone]])]=0 and [COUNTD([Home Phone]])]=1 then 0

            elseif [COUNTD([Business Phone]])]=0 and [COUNTD([Cell Phone]])]=1 and [COUNTD([Home Phone]])]=0 then 0

            elseif [COUNTD([Business Phone]])]=1 and [COUNTD([Cell Phone]])]=0 and [COUNTD([Home Phone]])]=0 then 0

            elseif [COUNTD([Business Phone]])]=0 and [COUNTD([Cell Phone]])]=1 and [COUNTD([Home Phone]])]=1 then 1

            elseif [COUNTD([Business Phone]])]=1 and [COUNTD([Cell Phone]])]=1 and [COUNTD([Home Phone]])]=0 then 2

            elseif [COUNTD([Business Phone]])]=1 and [COUNTD([Cell Phone]])]=0 and [COUNTD([Home Phone]])]=1 then 0 END