Count the number of times the airline is the unique flying a specific route

[brunolelli87]

Hey guys,

I've the following chart which shows me the routes (dimension) and the number of time each airline flow that specific route.

This is my expression:

count({<DASI ={'$(vData3)'},ROTA -= {'N/A - N/A'},ONGR={'0'}>} DISTINCT KEYI)

The dimensions are:
- ROTA
- NCIA 

As you can see here, the airline called AZU (the blue one), flew about 10 routes alone, with no competitor.

So, how can I create another chart in order to show, the number of routes each airline is flying "alone"?
I'm expecting something like this:

Any idea?

I don't even know how to start...

[brunolelli87]

Any idea?

[rwunderlich]

It's hard to confirm without sample data, but I think it's something like this.

1. DImension is NCIA (airline).

2. Count KEYI only for those ROTA where the count of NCIC = 1.

count({<ROTA = {"=count(NCIC)=1"}>} DISTINCT KEYI)

-Rob

[brunolelli87]

Hello Rwunderlich, how are you?

Thank for your kindly help, but unfurtunatelly, it's not working... I got some very small values, as you can see below

In this example, I'm expecting about 19 of AZU not only 2.

Please, find attached a Sample Data.
And again, thank you for your kindly support!

[rwunderlich · Accepted solution]

It works for me -- after adding DISTINCT and correcting my spelling.  If it doesn't work for you can you post the sample qvw or at least the exact expression you are using?

[brunolelli87]

Yes, of course!
Thank you so much per your time and assistance.

I've to say that your formula is working but the chart is not providing the correct answer, I mean, the numbers are wrong.

Please find attached a sample qvw, but I would ask you to send the formulas to me because I'm using QlikView Personal Edition so I'm not able to open any file from you.

From the Trial.qvw file, and keeping the "Date as 06/04/21", I'm expecting the following results:

Which represents: AZU for example, flew alone in 190 routes, performing 411 flights.

Can you still help me?
Thank you so much!

[rwunderlich]

I was Counting KEYI because I just copied your original post.  If you want a count of routes, I suppose you would count ROTA.  For flights, count the field that represents a flight. 

-Rob

[brunolelli87]

Now I get it...

I'm using a set analysis expression in order to define some specific criterias, and so I added this set analysis expression in both side of your formula, and it's working

=count({<DASI ={'$(vData3)'}, ONGR={'0'}, ROTA -= {'N/A - N/A'}, ROTA = {"=count({<DASI ={'$(vData3)'}, ONGR={'0'}, ROTA -= {'N/A - N/A'}>}DISTINCT NCIA)=1"}>} DISTINCT ROTA)

Before I was adding the set analysis expression at the begining or inside the new count and the value was not correct.

Thanks per your assistance...