Count values regardless selection

[david777]

Hello,

I have the following table.

VisitID	PersonID	YEAR
1	1	2014
2	1	2015
3	1	2016
4	1	2017
5	1	2018
6	1	2018
7	2	2015
8	2	2016
9	2	2017
10	3	2016
11	3	2017
12	3	2017
13	3	2018
14	4	2014
15	4	2015
16	4	2015
17	4	2016
18	4	2018
19	5	2016
20	5	2016

I want to count how many PersonID who had at least two VisitID during the period 2017-2018.

The answer should be 2 (PersonID 1 and PersonID 3) even if I make the selection 2018 for field YEAR.

Can anyone help me with this?

Best regards

[sergio0592]

Hi, it works with :

=count({<PersonID={"=count({<YEAR={'2017','2018'}>} VisitID)>2"}>} distinct PersonID)

[john_obrien · Accepted solution]

David,

This expression (also shown in the gauge chart below) works for me:

     Sum(If( Aggr( Count ( {<YEAR = {2017,2018}>} VisitID), PersonID)>=2,1,0) )

It may not be the most efficient way to calculate it, but it may be fine for a small data set.

[shiveshsingh]

Hi

check with this, values will not change even if you select 2018 in YEAR column

=count({<YEAR,PersonID={"=Count({<YEAR={'2017','2018'}>}VisitID)>2"}>}distinct PersonID)