How to exclude certain value of a field in an expression

[#1]

Hey guys,

I would like to you how to exclude certain value of a field in an expression. For example, I would like to count the number of userid, but without counting the value 0?

Regards and many thanks!

[#2]

You can try like this:

count({$<userid -= {0}>} distinct userid)

[israrkhan]

you can write set analysis.

1) count( {$<userid = {“*”} – {0} >} UserID ), this will count all userid, except userid =0

2) count({< userid -= {“0”}>}), this will do the same as above.

3) if you want to exclude many value, you can try like below:

count({< userid = {“*”}>} – {0, 11, 23} >} UserID), this will count all userid except userid = 0, 11, 23.

4) count({< userid -= {“0, 11, 23”}>} >} UserID). same as number 3..

Hope it helps.

and read the set analysis in the help, F1 for help... 🙂

[#4]

If I have another condition to add for UserID, say, {"=only(department)='sales'"} , how can I add two at the same time?

[#5]

count({$<userid -= {0}, department = {'sales'}>} distinct userid)

[israrkhan]

simply use the comma to separate the conditions, like below

=count({< userid -= {“0, 11, 23”} , department = {Sale}>} >} UserID)

[#7]

why you have two >} here? I have tried both, it seems to me there are some errors, did not work

[#8]

Hi IsrarKhan,

Just to note that there's a typo as you're enclosing the numbers 0, 11 and 23 within double quotes.

In this case they'll all be considered as one value, and not be treated as separate values to be excluded.

[israrkhan]

🙂 accepted, syntax mistake, as used to copy past....

but you got the idea and offcourse i think you can correct the syntax.

=count({< userid -= {0, 11, 23} , department = {'Sale'}>} UserID)    .. correct syntax.

EDIT:

and it depends , your value 0, 11 , 23 are number are strings, if numbers than it will work,

if they are strings then you need to use ' ' around each value, like

'0' , '11', '23' ...

[SunilChauhan]

count(distinct if(filedname<>0, UserID)

or

count(distinct if(UserID<>0, UserID)

or

count({$<UserID-= {'0'}>} distinct UserID)

hope this helps