Need help to build an expression

[#1]

Hello,

I need to count the number of distinct Order where Status<>'X' AND Quantity<>0.

There is the expression:

=count({<Status-={'X'} AND Quantity-={'0'}>}  DISTINCT Order)

But unfortunately this syntax doesn't work, you can't put an 'AND' there...

I really dunno how to do this in a different way...

May some experts help me for this please?

Thanks,

Best Regards,

Loïc

[swuehl]

Mayb try

=count({<Status-={'X'}, Quantity-={'0'}>}  DISTINCT Order)

[#3]

Your expression exclude all the Status='X' and all Quantity='0'.

But I want to exclude only the lines where Status='X' and Quantity='0'.

[manojkulkarni · Accepted solution]

count(distinct if(Status<>'X' and Quantity<>0,Order))

[paolocamerini]

I'd rather use

=count({<Status={'*'}-{'X'},Quantity={'*'}-{0}>}DISTINCT Order)

Tell us if it works

[swuehl]

You are right.

If you need to check on a per record base, you would need to use a key field that is unique for every combination of Status and Quantity per Order in the set analysis set modifier, or use the COUNT(IF(...) ) as ManojK has shown.

[effinty2112]

Swuehl's suggestion =count({<Status-={'X'}, Quantity-={'0'}>}  DISTINCT Order) looks good to me.

It's well worth it, when you are ready to tackle it, to become familiar with this very concise set analysis notation but it can be tricky until you get used to it.

<Status-={'X'}>

means recognise user selections in the Status field but remove records where Status = 'X'.

<Status=-{'X'}>

looks as if it is the same but it's not quite, note the position of '-'. is a shorthand for

<Status=*-{'X'}>

and when you think of it like this you can see that this means disregard user selections in the Status field, i.e. include all, but remove records where Status = 'X'.

Regards

[Peter_Cammaert]

How about this one?

=count({-<Status-={'X'}, Quantity-={'0'}>}  DISTINCT Order)

[#9]

I don't know why but i got the same result doing:

count(distinct if(Status<>'X' and Quantity<>0,Order))

or

count(distinct if(Status<>'X',Order))

It seems like a bug, the second argument is forgotten...

[#10]

I don't think this could work, because in reality i have other fields that must be included in the same expression...

It's more like

=count({<Date={"<=$(varDate)"}, Status-={'X'} AND Quantity-={'0'}>}  DISTINCT Order)

Where the Date value must be included and

Status and Quantity must be excluded.