Count if equal 1

[akmughal]

Hi there,

I'm not sure how to get distinct Group ID count if there is a single entry of a client.

So, in below example I wanted to count distinct group id of all clients who have just one entry date.

Client ID	Entry Date	Exit Date	Group ID
2	7/1/2020	8/20/2020	100
3	7/1/2020	8/20/2020	100
8	10/1/2020	12/20/2020	101
10	5/1/2020	6/20/2020	102
10	7/1/2020	8/20/2020	102
11	7/1/2020	8/20/2020	102

Result = 3

I'm trying this expression but does not work

=Count(aggr(if(count([Client ID]) = 1,[Group ID]),[Group ID]))

Could someone help how to write correct statement?

Thank you,

akmughal

[dwforest]

For the example you cited, you can use set analysis

Count({<[Exit Date]={"*"}>} [Group ID])

[albertovarela]

How about:    count(distinct {$<[Group ID]={"=aggr(count([Client ID]),[Group ID])=1"}>} [Group ID])

[albertovarela]

Actually, I think the distinct qualifier can be removed as we are already evaluating the distinctness through the set modifier so I'd go with:

count({$<[Group ID]={"=aggr(count([Client ID]),[Group ID])=1"}>} [Group ID])

[akmughal]

Hi-

It gives me the count of all entries but I'm looking for the unique count of client group id who has just one entry date.

Thanks,

Asif

[albertovarela]

Strange. Here's what I got using your sample data... 

[akmughal]

I apologize.. I meant to reply on other post.

It should give result 3 because client 2,3,8 and 11 entered once but have 3 unique group id.

Thank you,

Asif

[albertovarela]

Use this then: count(Distinct {$<[Client ID]={"=aggr(count([Entry Date]),[Client ID])=1"}>} [Group ID])

[akmughal]

Does that give you the result 3?

I'm running the statement on real data and does not get the accurate count.

[albertovarela]

Yes, I got 3.  Attached is the qvf