6 Replies Latest reply: Oct 18, 2016 7:42 AM by Tanya Ruppell RSS

    set analysis modifier with counts

    Tanya Ruppell

      Hi,

       

      I have data which is sites, ports, and services

      • A site has multiple ports
      • A port belongs to one site
      • Ports to Services is many to many

       

      I have a table with sites as the dimension.

      I want to add a count of the # of ports on that sites that carry all services that cross the site.

      So for example,

      if Site Alpha has 4 ports and 6 Services with the following distribution:

        • Port A carries 2 services
        • Port B carries 1 service
        • Port C carries 6 services
        • Port D carries1 service

       

      My table should report:

      Site A   1

      (counting only Port C)

       

      I am using the following to count ports with a hard-coded number of services:

           count (distinct {$<[port]= {"=count( distinct [service]) = 6"}>} [port])

       

      Now I am stuck trying to replace the hard-coded "6" with a count of the total number of services on the site for the specific row.

       

      Thanks for any help you can provide!

        • Re: set analysis modifier with counts
          Sunny Talwar

          So how do you know that Port C has all the the services? May be, for example, this is what we have

           

          Port A - a, b

          Port B - c

          Port C - b, c, d, e, f, g, h (Doesn't have a)?

           

          Or is that not possible?

            • Re: set analysis modifier with counts
              Tanya Ruppell

              In the set up at the beginning, I specified that Site A has 4 ports and 6 services. In your example, the site would have to have 8 services (I think you meant to put 7).

               

              I could add a measure to my table of count(distinct [service]) and it will return 6 in my example.

                • Re: set analysis modifier with counts
                  Sunny Talwar

                  So in total there can ever be 6 services? If that is true, what is the issue in hard-coding 6? I mean unless it can change, what is the issue in hard-coding?

                    • Re: set analysis modifier with counts
                      Tanya Ruppell

                      Sorry, I guess I wasn't very clear. That was just meant to be one example with one site. I used a hard coded value because I was trying to figure out the expression one piece at a time. The actual data has thousands of sites with a variable number of services.

                       

                      Let me expand my example:

                      Site A

                          Port 1 has services: a,b

                          Port 2 has services: c

                          Port 3 has services: a, b, c, d, e, f

                          Port 4 has services: e

                       

                      Site B

                          Port 1 has services: a, b, c

                          Port 2 has services: b, d

                          Port 3 has services: a, c, d

                       

                      My Table should look like this (I've added an extra column)

                      Site IDTotal # Services# Ports with all services
                      Site A61
                      Site B40

                       

                      I can get the total number of services column using count(distinct [service]).

                      I just have not been able to use this within the modifier for the # of Ports column to get the total number of services per site:

                           count (distinct {$<[port]= {"=count( distinct [service]) = 6"}>} [port])


                      Thank you.