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

[Kushal_Chawda]

@akmughal  try below

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

[akmughal]

It worked. Thank you!

[Kushal_Chawda]

@akmughal  Please accept solution

[albertovarela · Accepted solution]

Great to hear @akmughal ! Please accept as solution so the thread can be closed.