Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
bhargavikn
Contributor III
Contributor III

Count ID as zero if category has one specific value

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.

Labels (3)
4 Replies
Mark_Little
Luminary
Luminary

Hi

This should do it

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

 

Mark

bhargavikn
Contributor III
Contributor III
Author

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
Contributor II
Contributor II

Try this expression 

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

How about:

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

rwunderlich_0-1748874514939.png

 

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