MIN3 and MAX3 values in qliksense

[QS21]

Hi all,

I am trying to get Max3 employees month on month in a bar chart.

X-axis=Months and bars on each month will have 3 employees who came in top3 that month.

I used sum({<EMPLY_NAME={"=rank(aggr(Sum(Sales), EMPLY_NAME), 4, 1)<=3"}>} Sales) which is working perfectly.

Now the problem is with MIN3, I used

sum({<EMPLY_NAME={"=rank(aggr(-Sum(Sales), EMPLY_NAME), 4, 1)<=3"}>} Sales)  I used - infront of sum to get the reverse rank which seems fine but there are'-' NULLs in the sales column. so I getting the employee names with null in the MIN3 chart. I tried many conditions to exclude them but no use.

Someone pls suggest how to get the bottom3 employees whoc are not NULL.

Thanks in advance!

[justISO]

Hi, maybe with

sum({<EMPLY_NAME={"=rank(aggr(if(not isnull(Sales),-Sum(Sales)), EMPLY_NAME), 4, 1)<=3"}>} Sales)

[QS21]

Hi, I have tried it and am still not getting the result:

[justISO]

I believe your max3 then not working properly too, because your ranking employees total sales (not per each month) and representing those top3 employees sales, not searching top3 employees by sales each month. So you should aggregate not by Employee only, but by Date too, something like this aggr(Sum(Sales), DATE, EMPLY_NAME).

How I would calculate min3 (as I'm not very friendly with set analysis :/):

if(Aggr(Rank( if(sum(Sales)<>0,-Sum(Sales)) ),DATE,EMPLY_NAME) <= 3, Sum(Sales),Null())

and in Add-ons->Data handling uncheck 'Include zero values'