topic Re: Top N values in QlikView

Top N values

Tue, 20 Aug 2013 10:58:56 GMT

Hi All,

I have a requirement to show top N values,its Sum and Others Sum with respect to customer as shown below.

Input

costumer, sales, name
1,10,AA"
2, 20, "BB"
3, 30, "CC"
4, 40, "DD"
5, 50, "EE"
6, 60, "FF"
7, 70, "GG"
8, 80, "HH"
9, 90, "II"
10, 100, "JJ"
11, 110, "KK"
12, 120, "LL"
13, 130, "MM"
]

Want output as

Customer,sales

9, 90

10, 100

11, 110

12, 120

13, 130

Top 5 total,550

Others total,360

Here I have shown like top 5. But this should be dynamic as user enters using variable. How can we achieve it without using rank,aggr,dimention limits as these are causing performnace issue as we having lot of records in table. In scripting or using other method please advice.

Re: Top N values

tresB — Tue, 20 Aug 2013 11:10:29 GMT

Hello Suneela,

If this is in continuation to your another post, then let me tell you,

to make your expression dynamic, somewhere in the front end expression you have to set a limit (using the variable), so that the results varry with user selection/slider movement. Therefore, the use of variable in the expression is certain, now you have to decide the logic, use dimension limit or aggr or something else.

Easiest would be, If it's OK to sort your chart by the expression descending, then you can just set a max number of $(vYourVariable) on the presentation tab or limit the dimension (use same variable to make it dynamic), and not have to fiddle with calculated dimensions.

Hope this helps.

Re: Top N values

Kushal_Chawda — Tue, 20 Aug 2013 11:15:46 GMT

see the attached one

Re: Top N values

Tue, 20 Aug 2013 17:03:25 GMT

In the above image the red part should not be kept as there will be too much records. So can I get output like below.

Customer sales

8 80

9 90

10 100

11 110

12 120

13 130

Top6 630

Others 280

Only two columns with above info. Waiting for ur idea.

Re: Top N values

Tue, 20 Aug 2013 17:05:06 GMT

I mean to say in practical there will be too much records so I dont want to show others details. Just sum value of others needed. Hope the issue is clear.

Re: Re: Top N values

Kushal_Chawda — Wed, 21 Aug 2013 04:56:00 GMT

see the attached one

Re: Re: Top N values

Wed, 21 Aug 2013 10:27:45 GMT

Thanks for your reply. I want to get the total label as below. I have tried with if condition in label but not working. Can I get it.

Thanks lot.

Re: Top N values

Kushal_Chawda — Wed, 21 Aug 2013 11:40:46 GMT

if condition will not work for label.. but i think you can easily indentify the Total for Top & others as it is divided by the group in dimension.. so i don't think there is necessity to write total label as Total Top 7..