topic Re: Automatic filter on sheet select in QlikView

Automatic filter on sheet select

Thu, 27 Feb 2014 11:29:08 GMT

Hi,

i want to create an automatic filter on sheet activating that select only the Top 10 items of a list.

The ranking formula is the following:

=aggr(if(rank(sum(value))<=10,State),State)

How can i realize it?

Re: Automatic filter on sheet select

sujeetsingh — Thu, 27 Feb 2014 11:30:15 GMT

you can set action on close sheet

Re: Automatic filter on sheet select

Thu, 27 Feb 2014 12:27:38 GMT

Try that on OnActivate action, Selection, select in Field

Choose your field: State

And put that in the search string:

replace( '(' & concat($ YourExpression, '|') &')', ' ', '?')

where YourExpression is aggr(if(rank(sum(value))<=10,State),State)

As usual, if you do selection instead of the user, you need to warn it with a textbox or sth similar.

Tell me

Fabrice

Re: Automatic filter on sheet select

Thu, 27 Feb 2014 13:10:51 GMT

Hi Fabrice,

it seems to be a good idea, but it doesn't work.

No filter is applied..

Re: Automatic filter on sheet select

Thu, 27 Feb 2014 14:27:07 GMT

1) Clear field first in action 1 (if not the first 10 will be searched into the current state)

2) in action 2, do not forget the = sign before the replace() function

3) in sum(value), it is also possible to insert a set analysis (or clear specific fields first) because this sum will depend on the current state

But you are right it does not work: a button with this action works, the OnActivate does not work

A workaround:

create a variable with =replace (xxxxx)

and the action becomes ='$(newVariable)'

(do not forget the quotes)

Fabrice

Re: Automatic filter on sheet select

Thu, 27 Feb 2014 15:47:35 GMT

I tried with a button, but it doesn't work yet.

I did the following:

1) create button

2) rule1: "Clear filters in field Stato"

3) rule2: "Set filter in field "Stato"" like this:

=replace( '(' & concat($(aggr(if(rank(sum(Imponibile))<=10,Stato),Stato)), '|') &')', ' ', '?')

Only the first rule works...

Where is the problem?

Re: Automatic filter on sheet select

Thu, 27 Feb 2014 15:55:39 GMT

do a button with just rule2 (it should work without pb). Remove $( before aggr

as I told you, I created a variable that is put into the action

Fabrice

What I have and works:

=replace( '(' & concat(aggr(if(rank(sum(montant)) < 10, date_creation), date_creation), '|') &')', ' ', '?')

Re: Automatic filter on sheet select

Thu, 27 Feb 2014 16:50:48 GMT

Great!

Now it works!