Count ID as zero if category has one specific value

[bhargavikn]

Hello,

I'm looking for front end expression/solution for below issue. please suggest me.

I need the count of distinct ID, where state of ID doesn't have 'Cancelled'.

ID SubID State Required output

101 101-1 Resolved 0

101 101-2 Inprogress 0

101 101-3 Closed 0

101 101-4 Cancelled 0

102 102-1 Resolved 1

102 102-2 Inprogress 1

102 102-3 Closed 1

103 103-1 Cancelled 0

103 103-2 Closed 0

Required output is Count(distinct ID)  as 1 that is '102' where it does not have Cancelled state

I have tried, Count({<State<>{'Cancelled'}>}distinct ID) but it is giving me 0 for only cancelled state row. but I need 0 for all states for that ID

Please do needful, your help much appreciated.

Thank you.

[Mark_Little]

Hi

This should do it

Count({<[State Required]-={'Cancelled'}>}Distinct ID)

Mark

[bhargavikn]

Thank you for reply. This is giving 0 for  only cancelled state row and 1 for remaining all states. 

but I want 0 for all states when one ID has at least one Cancelled state.

[Dementor]

Try this expression 

sum(distinct if(not wildmatch(aggr(nodistinct Concat( State, '|'),ID),'*Cancelled*'),1,0))

[rwunderlich]

How about:

if(Max(TOTAL<ID> Match(State,'Cancelled')),0,1)

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com