Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
brunolelli87
Creator II
Creator II

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

Hey guys,

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

brunolelli87_0-1617670254151.png

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.

brunolelli87_0-1617713884542.png

 

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:

brunolelli87_1-1617670361488.png

Any idea?

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

 

1 Solution

Accepted Solutions
rwunderlich
MVP & Luminary
MVP & Luminary

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?

2021-04-07_08-51-28.png

View solution in original post

7 Replies
brunolelli87
Creator II
Creator II
Author

Any idea?

rwunderlich
MVP & Luminary
MVP & Luminary

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
Creator II
Creator II
Author

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

brunolelli87_0-1617792883492.png

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
MVP & Luminary
MVP & Luminary

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?

2021-04-07_08-51-28.png

View solution in original post

brunolelli87
Creator II
Creator II
Author

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:

brunolelli87_0-1617829349376.png

 


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

Can you still help me?
Thank you so much!

rwunderlich
MVP & Luminary
MVP & Luminary

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
Creator II
Creator II
Author

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