Automatic filter on sheet select

[#1]

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?

[sujeetsingh]

you can set action on close sheet

[#3]

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

[#4]

Hi Fabrice,

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

No filter is applied..

[#5]

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

[#6]

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?

[#7 · Accepted solution]

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), '|') &')', ' ', '?')

[#8]

Great!

Now it works!