topic Re: How to create dynamic selection list in QlikView

How to create dynamic selection list

Wed, 13 May 2015 13:58:20 GMT

Hello everyone

I've looked through the forum and didn't find an answer which match with my need.

Here what i'm trying to do

I have a list of store. (store_id)

Each store is doing actions (id_action)

I want to create a dynamic selection list.

I want to show in that list only the store with an amount of action > $(my_variable) (or let's say 50 for the exemple)

So i'm doing new objet > Selection list.

Then i choose <Expression> in the field list.

Here is my expression

if(count(distinct(id_action))>=50,store_id).

My selection list is empty. Nothing appear inside

Any help could be usefull

Thanks

Re: How to create dynamic selection list

petter — Wed, 13 May 2015 19:12:36 GMT

You can use advanced search in a list box together with your dynamic expression to achieve that.

Click on the title of the list box or click the magnifying glass (search icon) and type:

=Count(DISTINCT id_action) >= 50

and press enter.

You should now have a selection of only those stores...

If you want to store this search you can even save it as a bookmark (add bookmark) - and specify when adding it that it should be applied on top of current selection. This bookmark will not store the list of the individual store but actually your advanced search which is your expression.

Re: How to create dynamic selection list

petter — Wed, 13 May 2015 19:18:44 GMT

BTW....

Your expression: if(count(distinct(id_action))>=50,store_id)

shouldn't have ( ) surrounding the id_action. The DISTINCT is just a prefix before the field name which can be used with most aggregation functions like Count.

So for it to work at least you have to write:

If( Count(DISTINCT id_action)>=50 , store_id )

Re: How to create dynamic selection list

oleg_datsenko — Wed, 13 May 2015 19:45:52 GMT

Hi guys,

Petter, are you really sure that Count() function can return a value more than 1 ?

I'd like to offer to use SUM above the Count, like this:

if(

sum(

count(distinct id_action))>49,

store_id)

Oleg

Re: How to create dynamic selection list

petter — Wed, 13 May 2015 20:09:22 GMT

Not at all - I just corrected an obvious error in his expression ... in a hurry ... I think that my first post is probably what I would have wanted to implement...

Re: How to create dynamic selection list

stigchel — Wed, 13 May 2015 20:23:46 GMT

I would suggest the following expression as List box Expression:

=Aggr(Only({<store_id={"=Count(DISTINCT id_action)>50"}>} store_id),store_id)

Re: How to create dynamic selection list

oleg_datsenko — Wed, 13 May 2015 20:41:57 GMT

Don't worry. Actually I'm not sure about returning only 1 value Maybe you are right. And using SUM is not necessary. A bit tired...and can make a mistake.. sorry

BTW

I think that count(id_action) will return the same as count(distinct id_action) in this case. ID is something distinct "by default" in most cases )

Re: How to create dynamic selection list

petter — Wed, 13 May 2015 20:50:49 GMT

Well I was a bit tired and didn't study the expression and what it would do much at all ... I just commented on the obvious error in the expression - and being a bit too lazy or quick I didn't even consider the context or implications of using the rest of the expression.... So I think you are the one that is right here