Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
becs_parkinson
Contributor
Contributor

Count if name appears in Q2 but not Q1

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).

NameCase NumberQuarter
John101Q1
Sarah102Q1
Robert103Q1
John104Q2
Andrew105Q2
Sarah106Q2
Chris107Q2

 

Appreciate any advice!

Thanks,

Becs

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

May be this

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

View solution in original post

5 Replies
sunny_talwar

May be this

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

you dont like to make SQL query hear

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

Channa
Channa
Specialist III
Specialist III

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

Channa
becs_parkinson
Contributor
Contributor
Author

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)