Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jacek_stypulkow
Contributor II
Contributor II

Count if

Hello,

Can you help please?

Looking for expression to count the Ref ID from those ClientID who appear in 2017 only leaving out those who appear in both years.

The result coming form below example should be 3 as D and E appears only in 2017. Any ideas please?

 

Ref IDClientIDYear
1A2016
2A2017
3B2016
4B2016
5B2017
6C2016
7C2017
8D2017
9E2017
10E2017

Regards

Jacek

1 Solution

Accepted Solutions
Anonymous
Not applicable

=count({<ClientID=P({<Year={2017}>})> - <ClientID=P({<Year={2016}>})>} distinct RefID)

View solution in original post

7 Replies
rupamjyotidas
Specialist
Specialist

This might help you to work on the equation

Count(If(Aggr(Count(Distinct Year),ClientID)=1,ClientID))

Anonymous
Not applicable

=count({<ClientID=P({<Year={2017}>})> - <ClientID=P({<Year={2016}>})>} distinct RefID)

OmarBenSalem

Let's assume that we only want to work with only 2017; we have years from 2000 to 2017, it won't be pleasant to write all the years as you did in your expression (which works by the way)

How should we tell sense to only count those who have Only(year) = 2017?

Thanks !

Anonymous
Not applicable

Reasonable question...  I think this should work:

=count({<ClientID=P({<Year={2017}>})> - <ClientID=P({<Year-={2017}>})>} distinct RefID)

sunny_talwar

May be like this:

Count(DISTINCT {<[Client ID] = {"=Count(DISTINCT Year) > 0"}>}[Client ID])

sunny_talwar

Or this:

Count(DISTINCT {<[Client ID] = {"=Count(DISTINCT Year) > 0"}, Year = {2017}>}[Client ID])

jacek_stypulkow
Contributor II
Contributor II
Author

It works perfect, thanks.