Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld May 10-12, Online and Free! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jacek_stypulkow
Contributor
Contributor

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
mov
Champion III
Champion III

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

mov
Champion III
Champion III

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

View solution in original post

OmarBenSalem
Partner
Partner

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 !

mov
Champion III
Champion III

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
Contributor
Author

It works perfect, thanks.