Solved: table Ranking TOP 20% - Qlik Community

beginner91 · ‎2019-02-05

hello all,

i have a table and want to show the TOP 20% of it like:

Headline1 / Headline2 / Headline3

Name / Name / count of serveral numbers

i wanted to show the top 20% of the counted numbers.

can someone help me?

greetz

pradosh_thakur · ‎2019-02-06

count({<Name={"=Rank(count(mistakes))/Count(distinct total {1} Name)<=0.2"}>}mistakes)

Learning never stops.

View solution in original post

pradosh_thakur · ‎2019-02-06

Hi

Follow the exact steps with your dummy data and it will work
1: Load your data as it is , dont change the dimension and measure name or case as of now

load * inline [
Name,number,mistakes
house,125343,RQ00001
house,125343,RQ00002
house,125343,RQ00003
house,125343,RQ00004
house,125343,RQ00005
house,125343,RQ00006
house,125343,RQ00007
house,125343,RQ00008
house,125343,RQ00009
garden,343212,RQ00010
garden,343212,RQ00011
garden,343212,RQ00012
garden,343212,RQ00013
garden,343212,RQ00014
garden,343212,RQ00015
garden,343212,RQ00016
garden,343212,RQ00017
garden,343212,RQ00018
swimmingpool,454905,RQ00019
swimmingpool,454905,RQ00020
swimmingpool,454905,RQ00021
swimmingpool,454905,RQ00022
swimmingpool,454905,RQ00023
swimmingpool,454905,RQ00024
swimmingpool,454905,RQ00025
swimmingpool,454905,RQ00026
swimmingpool,454905,RQ00027
swimmingpool,454905,RQ00028
swimmingpool,454905,RQ00029
test,787843,RQ00030
test,787843,RQ00031
test,787843,RQ00032
test,787843,RQ00033
test,787843,RQ00034
test,787843,RQ00035
test,787843,RQ00036
test,787843,RQ00037
test,787843,RQ00038
test,787843,RQ00039
test,787843,RQ00040
];

2: reload it.
3: Add a table
4: Add dimension -> name
5: Add dimension -> number
6: Add measure - > count( {<Name={"=Rank( count(mistakes),4,1)/Count(distinct total {1} Name)<=0.25"}>}mistakes)

This will give you only one row which is it 25% of top rows. If you change it to 20% no rows will show up as there are 20% of 4 is less than 1. That's why i haev asked you to add more data and may be that's why it was working for me and not for you. You need to add more names then even 20% will work.

Regards
Pradosh

Learning never stops.

View solution in original post

Channa · ‎2019-02-05

you want to see top 20 headlines1??

not clear bro

Channa

beginner91 · ‎2019-02-05

no i want to see the headline 3 ranked for the top 20%.
and headline 3 are counted numbers.

pradosh_thakur · ‎2019-02-05

Do you want to see top 20 that contribute the top20 % ?

if yes have a look

https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-a-Pareto-Analysis-Revisited/ba-p/1473684
https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-a-Pareto-Analysis/ba-p/1468497
https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-an-ABC-Analysis/ba-p/1472867

Learning never stops.

beginner91 · ‎2019-02-05

isnt there a easier solution? iam not really good at writing in the editor...

i want the show the top 20% counted numbers on the right side. i have a screenshot how the table is builted.

the table has like 3351 counted numbers.

beginner91 · ‎2019-02-05

guys?

pradosh_thakur · ‎2019-02-05

if you want to see the rows that constitute top 20% then u have to use the above solution. if you want 20 rows out of 100 that is top 20% there is another approach we can think of. Which one you are looking for ?

Learning never stops.

beginner91 · ‎2019-02-05

iam wirting like: rank(count([1]),1) but its just showing me 1,2,3 but i wanted to see the counted numbers within top 20%

pradosh_thakur · ‎2019-02-05

Can you please add some sample data nd show the output, it will be clear what exactly you want .

Learning never stops.

beginner91 · ‎2019-02-05

i cant show u any details about the data sry... but check up my excel data. you have like 3600 name and 3600 numbers and every mistake is in the list. i count every mistake and just want to show the top 20% of the guys which made the most mistakes