Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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 )
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
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...
I would suggest the following expression as List box Expression:
=Aggr(Only({<store_id={"=Count(DISTINCT id_action)>50"}>} store_id),store_id)
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 )
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