Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Filter Top 25 with multiple expression criteria?

I have multiple expressions that I would like to filter the Top 25. The criteria is to find how many devices have errors associated with them.

Expression 1 counts distinct devices

Expression 2 counts number of distinct devices with errors

Expression 3 is errors / devices (%)

I have the customer names as the dimension.

The issue with simply filtering by Expression 3 is that some of my customers only have 1 devices with 1 error, so their 100% error rate shows up in the Top 25. I'm more interested in addressing the errors on the customers with the top 25 devices (ie: I'd rather focus on a customer with 87% error rate but has 100 devices instead of a customer with 1 device and a 100% error rate). It doesn't have to be 'Top 25' exactly, it can be a set number (ie: ranking of all customers with 40+ devices by error %), but I somehow need to incorporate both of these into the equation.

Based on information from looking this issue up on the site, I attempted to create a calculated dimension of =if(Aggr(rank(count(distinct(devices with errors)/count(distinct(total devices), devices <=25, devices, null()) but that hasn't worked.

My data is currently in a Pivot Table / Chart format. QV 11.

Any suggestions?

Tags (2)
1 Reply
MVP
MVP

Re: Filter Top 25 with multiple expression criteria?

Hi Taylor,

instead of a calculated dimension, you could use advanced search in set analysis and populate the same search criteria in all of your expressions. I described it with a lot of detail in my blog article here:

QlikView Blog Q-Tip #5 – Show Top Performers. Common but not too trivial. | Natural Synergies

The added complexity in your case is the number of different conditions that you need to apply. You can decide whether you should nest your Set Analysis conditions or apply Boolean intersection (multiplication) to them.

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

Community Browser