table Ranking TOP 20%

[beginner91]

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

[beginner91]

i really dont understand why its not working...  its showing me for all data 0.  Do you have any idea Pradosh where the problem can be??? my data is build up like in the excel sheet. I really cant explain where the bug is...

[beginner91]

any ideas???

[beginner91]

Check the Excel. Maybe its easier to unterstand where the problem is. thank you for your support.

[pradosh_thakur · Accepted solution]

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

[beginner91]

thank you every much. I gona try it. i just wanted to say that iam working with Qlik sense Server not Desktop. Could there be a problem?

[pradosh_thakur]

I don't think so, I have tried it in both. You can add dummy data in a dummy app first , check the expression , add more data , validate it, then use it in your main dashabord/app.

[beginner91]

is there a problem when the dimension is called like:

count(distinct total {1}garden house)

and the garden house is my dimension but when its green qlik cant understand it?

i really dont know where the bug is. your expression is correct. its working on qliksense desktop.
when i use it in qliksense server with the correct dimension names its not working... but count like count(xyz) its counting my numbers.

[pradosh_thakur]

In dimension it may or may not work but should work as a measure/expression if used in correct context.

[pradosh_thakur]

Is your question answered? If yes pleasae mark the ansers which are helpful so that it can help others.

[siddharthsoam]

Hi 

According to what i understood, First you have to find the top 20% of mistakes in a table, you can use the below expression

sum(mistakes)*100/sum(total mistakes)

Now you have to display top 20 names in top 20% you can use

if(rank(sum(mistakes)*100/sum(total mistakes))<count(total names)*.2,customer,Null())

This should suffice..