Count if name appears in Q2 but not Q1

[becs_parkinson]

Hi,

I'm looking for some help with the following...

I have the following data set. I want to count the number of names with a case number, where the name appears in Q2 but did not appear in Q1. In this instance, the answer is 2 (Andrew and Chris appear in Q2 but not in Q1).

Name	Case Number	Quarter
John	101	Q1
Sarah	102	Q1
Robert	103	Q1
John	104	Q2
Andrew	105	Q2
Sarah	106	Q2
Chris	107	Q2

Appreciate any advice!

Thanks,

Becs

[sunny_talwar · Accepted solution]

May be this

Count(DISTINCT {<Name = p({<Quarter = {'Q2'}>})-p({<Quarter = {'Q1'}>})>} Name)

[Channa]

you dont like to make SQL query hear

select * form table where name not in (select Name from table where name='q1')

[Channa]

bro why u not use E() hear to exclude q2 from q1

[becs_parkinson]

thanks! this works! I have no idea how (a bit too complex for me) but checking against the raw data is is correct!

[sunny_talwar]

That should work too but with intersection (*) instead of minus (-)

=Count(DISTINCT {<Name = p({<Quarter = {'Q2'}>})*e({<Quarter = {'Q1'}>})>} Name)