topic Re: set analysis modifier with counts in App Development

set analysis modifier with counts

Anonymous — Fri, 14 Oct 2016 19:17:47 GMT

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 — Fri, 14 Oct 2016 19:28:18 GMT

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

Anonymous — Fri, 14 Oct 2016 20:06:14 GMT

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 — Fri, 14 Oct 2016 20:21:58 GMT

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

Anonymous — Fri, 14 Oct 2016 20:43:26 GMT

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.

Re: set analysis modifier with counts

sunny_talwar — Fri, 14 Oct 2016 21:17:33 GMT

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)

Re: set analysis modifier with counts

Anonymous — Tue, 18 Oct 2016 11:42:20 GMT

Thanks again, Sunny!