Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi
This should do it
Count({<[State Required]-={'Cancelled'}>}Distinct ID)
Mark
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.
Try this expression
sum(distinct if(not wildmatch(aggr(nodistinct Concat( State, '|'),ID),'*Cancelled*'),1,0))
How about:
if(Max(TOTAL<ID> Match(State,'Cancelled')),0,1)
-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com