topic Top / Least 10 in straight table with corresponding total in QlikView

Top / Least 10 in straight table with corresponding total

Thu, 01 Jul 2010 16:29:39 GMT

Hi,

I want to display top and least 10 categories (2 charts) based on Sales. I am using straight table to display these results. I can give max number as 10 in the prsentation tab, but the problem is that the Total displaying at the top is still for all categories. Is there any way to display the top/least 10 catgories with the corresponding total. Please look at the attachment.

I tried using rank function, but I am not able to get it work for 2 dimensions.

Thanks.

Top / Least 10 in straight table with corresponding total

Fri, 02 Jul 2010 07:46:36 GMT

This is something I need to do in the future for upcoming reports.

Anyone got ideas?

Top / Least 10 in straight table with corresponding total

Fri, 02 Jul 2010 08:29:13 GMT

If I've read this correctly, all you need to do is click once on Sales1, then right click over it, and select Sort.

This moves the Sort from the Product to the Sales1 column and allows you to sort ASC or DESC.

Hope this helps.

Top / Least 10 in straight table with corresponding total

Fri, 02 Jul 2010 11:23:07 GMT

Kindly use this function, it will work..

sum({<Vendor={"=rank(sum(Amount))<=10"}>} Amount)

Top / Least 10 in straight table with corresponding total

Sun, 04 Jul 2010 06:43:02 GMT

Graeme, my concern is about getting top/least 10 categories. I agree sorting gives the desired result, but not for the top/least 10.

Top / Least 10 in straight table with corresponding total

Sun, 04 Jul 2010 06:45:03 GMT

Thanks Ankit for the reply. Unfortunately, I am not able to get the things right. Can you please explain with the example I have posted.

Top / Least 10 in straight table with corresponding total

Sun, 04 Jul 2010 09:59:18 GMT

If you want to display top 10 categories, you can use rank(expr) function in order to restrict them to display top 10 results.

Top / Least 10 in straight table with corresponding total

Tue, 06 Jul 2010 13:40:09 GMT

I tried using rank function, but ynable to get it right. I have two dimension (Product & Category). There are multiple products having the same category. So, i am not able to get the top 10 with the correct 'total' displayed.

It will be great, if you can look at the attached QVW file in the original post.

Top / Least 10 in straight table with corresponding total

Wed, 07 Jul 2010 16:24:34 GMT

Can someone please help me out....

Top / Least 10 in straight table with corresponding total

nathanfurby — Wed, 07 Jul 2010 17:22:33 GMT

Hi Santhosh. Take a look at the attached file.

Sorry, but I did not have time to get it working with the two dimensions Product and Category, so removed Product to simplify things and maybe get you going on finding an answer.

Top / Least 10 in straight table with corresponding total

Wed, 07 Jul 2010 17:46:00 GMT

Thanks Nathan.

But unfortunately I need the other dimension (Product) also, because multiple product will have same category and hence the result is wrong. I tried using rank function in dimension, but didn't get it right. Is there any other way of doing it.?

Also, please let me know how to get least 10 categories. Thanks.

Top / Least 10 in straight table with corresponding total

Wed, 07 Jul 2010 20:02:36 GMT

I got it to work for the top 10 with two dimension. Below is the expression which I have used.

'=IF(AGGR(RANK (Total(sum( {<Range= {'0 - 10', '11 - 20'}>} Sales3 ))),Product, Category)<=10,sum( {<Range= {'0 - 10', '11 - 20'}>} Sales3 ))'.

I need to figure out to work it for least 10 now.

Top / Least 10 in straight table with corresponding total

Thu, 08 Jul 2010 06:05:55 GMT

Atlast, I got the answer right. I am able to get the Top / Least rank. I have attached the result and hope it helps someone.

Thanks.

Top / Least 10 in straight table with corresponding total

Thu, 08 Jul 2010 07:55:25 GMT

Thanks Santhosh... 🙂

Top / Least 10 in straight table with corresponding total

nathanfurby — Thu, 08 Jul 2010 09:39:01 GMT