topic Count values regardless selection in QlikView

Count values regardless selection

david777 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have the following table.

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 — Mon, 05 Mar 2018 18:57:31 GMT

Hi, it works with :

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

john_obrien — Mon, 05 Mar 2018 19:02:59 GMT

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 — Tue, 06 Mar 2018 06:34:46 GMT

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)