topic Count ID as zero if category has one specific value in App Development

Count ID as zero if category has one specific value

bhargavikn — Fri, 30 May 2025 07:21:06 GMT

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 — Fri, 30 May 2025 08:10:14 GMT

This should do it

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

Mark

bhargavikn — Fri, 30 May 2025 08:33:12 GMT

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 — Fri, 30 May 2025 10:42:49 GMT

Try this expression

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

rwunderlich — Mon, 02 Jun 2025 14:28:56 GMT

How about:

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