cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## set analysis modifier with counts

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.

1 Solution

Accepted Solutions
MVP

Give this a shot:

=Count(DISTINCT {\$<[port]= {"=Count(DISTINCT [service]) = Aggr(NODISTINCT Count(DISTINCT service), site)"}>} [port])

I making an assumption here, you can tell me that this is not true and we can look for an alternate solution.

Assumption: No two site will have a common port. For instance

Site1

Ports - a, b, c, d

Site2

Ports - e, f, g, h (this site won't have any port names which are in Site1)

6 Replies
MVP

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?

Anonymous
Not applicable
Author

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.

MVP

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?

Anonymous
Not applicable
Author

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 ID Total # Services # Ports with all services Site A 6 1 Site B 4 0

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.

MVP

Give this a shot:

=Count(DISTINCT {\$<[port]= {"=Count(DISTINCT [service]) = Aggr(NODISTINCT Count(DISTINCT service), site)"}>} [port])

I making an assumption here, you can tell me that this is not true and we can look for an alternate solution.

Assumption: No two site will have a common port. For instance

Site1

Ports - a, b, c, d

Site2

Ports - e, f, g, h (this site won't have any port names which are in Site1)

Anonymous
Not applicable
Author

Thanks again, Sunny!