Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead 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.

Thanks for any help you can provide!

1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

6 Replies
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?

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.

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?

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 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.

sunny_talwar

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!