topic Dynamic Button to Select Top n Values in QlikView

Dynamic Button to Select Top n Values

Mon, 01 Aug 2011 17:07:02 GMT

Hi,

I want to have a button that when pressed selects the top n values of a field but I also would really like a user to be able to input how many values and which of the fields (Cost, Revenue, Margin) it selects over.

In the button options I have tried 'Select in Field' but don't know what to put for the Search String. I also don't know how to incorporate inputs into a buttons functionality.

Any help would be much appreciated!

Dynamic Button to Select Top n Values

danielrozental — Tue, 02 Aug 2011 01:13:53 GMT

Can't you just use a straight table, you can set how many rows are seen by changing "max number" in the presentation tab, you can use a variable to set that.

Dynamic Button to Select Top n Values

danielrozental — Tue, 02 Aug 2011 01:24:12 GMT

If the above doesn't help you can try something like this

="=rank(aggr(sum(Sales),Customer))<=" & variableN

Dynamic Button to Select Top n Values

Tue, 02 Aug 2011 08:09:19 GMT

I could have a separate table where users could select the contents but I'd rather have a shiny button on each page of graphs that selects the best and worst. I tried =rank(aggr(sum(Cost), Customer))<=10 in the search string but it doesn't seem to do anything.

Dynamic Button to Select Top n Values

gandalfgray — Tue, 02 Aug 2011 08:23:37 GMT

You could use Input Field to set a variable (vLimit in my example).

Use that variable in the Presentation->Max Number setting of your chart.

Re: Dynamic Button to Select Top n Values

lironbaram — Tue, 02 Aug 2011 08:40:21 GMT

hei

attach is an example

maybe it can help you

what the user will have to do is select the measure for filter

number of top Customers

and the table will filter

Re: Dynamic Button to Select Top n Values

Tue, 02 Aug 2011 10:20:44 GMT

I only have personal edition so can't open other people's files. Is it possible for you to explain what you've done with some code?

Thanks!

Re: Dynamic Button to Select Top n Values

lironbaram — Tue, 02 Aug 2011 10:53:35 GMT

ok let try

attach is an excel file which is my sorce data

my script looks like this:

Data:

LOAD CustomerID,
CustomerName,
Sales,
Cost,
Profit
FROM

(ooxml, embedded labels, table is גיליון1);

LOAD * INLINE [
Meas
sum(Sales)
sum(Cost)
sum(Profit)
];

then i have this objects in the appliction:

list box of the filed Meas

input box for a Varaible with the name vNum

Chart of Stright Table with

dimension: =aggr(if(rank($(=only(Meas)) )<=vNum,CustomerName),CustomerName)

with the box: Supress when null is values - Checked

expressions:

sum(sales)

sum(profit)

sum(cost)

try to bulid a qvw file like that and then follow what i wrote in the previous post

Dynamic Button to Select Top n Values

SunilChauhan — Tue, 02 Aug 2011 10:58:24 GMT

hello ,

Take i inline like below

Load * Inline [

ColName

Top1

Top2

Top3

Top N

];

in chart u want n max

go to presentation and in max number shown option write code below

If(ColName='Top1',1,if(ColName='Top2',2,...........))

hope it will work for you

thanks

Sunil Chauhan

Dynamic Button to Select Top n Values

Tue, 02 Aug 2011 11:42:26 GMT

You can use a Slider control to let the user select a value into a variable. Then in the Presentation tab, in the Max visible number, reference the variable used in the Slider control.

Re: Dynamic Button to Select Top n Values

rustyfishbones — Mon, 16 Feb 2015 14:17:19 GMT

You can use

='(' & CONCAT(IF(AGGR(RANK(Sum(Sales)),Customer) <= 5,Customer),'|') & ')'

and you could create a variable in place of the <=5 so the user can dynamically select the Top N values

This will Select the Top 5 Customer based on Sales

Regards

Alan