topic Need help for creating a set expression in QlikView

Need help for creating a set expression

ananyaghosh — Thu, 21 May 2015 10:25:16 GMT

HI,

I have following tables:

Employee:

load * Inline [
Employee ID, Age, Customer Name, Sales
Sandip Ghosh, 34, Customer1, 1000
Ananya Ghosh, 29, Customer2, 1200
Amit Das, 35, Customer3, 3000
Kaushik Dhar, 55, Customer4, 5400
Sudhir Ghosh, 74, Customer5, 5980
Sandip Ghosh, 34, Customer6, 54670
Sandip Ghosh, 34, Customer7, 45675
Kaushik Dhar, 55, Customer8, 15400
Kaushik Dhar, 55, Customer9, 45400
Sudhir Ghosh, 74, Customer10, 6598
Ananya Ghosh, 29, Customer2, 16200
Sudhir Ghosh, 74, Customer5, 5980
Sudhir Ghosh, 74, Customer6, 2980
Ananya Ghosh, 29, Customer2, 12900
Ananya Ghosh, 29, Customer2, 12070

];

Now I have to show top three Employee whose total means sum of sales is highest.

For that I have used the following expression

=Only({$<Sales={"$(=Max(Total Sales,3))"}>}Sales)

But the above expression only display Top three employee whose sales are highest with only highest sales amount. But I want top 3 employee whose sum of sales are highest.

Thanks,

Sandip

Re: Need help for creating a set expression

rakesh_kumar — Thu, 21 May 2015 10:34:09 GMT

Hi Sandip,

This thread could be helpful for you:

Top Performers; Set Analysis and Aggr Question

Regards,

Re: Need help for creating a set expression

avinashelite — Thu, 21 May 2015 10:37:33 GMT

Hi Sandip,

If your using charts, then you can make use of the dimension limits, which will be very help full.

go to chart properties and dimension limits , show top dimension with your desired number ...

if you want it using expression try with Rank function

Re: Need help for creating a set expression

Thu, 21 May 2015 10:56:55 GMT

Hi Sandip,

please see attached

hope that helps

Joe

Re: Need help for creating a set expression

Anonymous — Thu, 21 May 2015 11:11:36 GMT

Hi Sandip

Try this this is semple and easy

=IF (aggr(rank( sum(Sales) ,0) , Sales )<= 3 , sum(Sales) )

Regards

Harsha