Count distinct Values from a list row

[JonesBeach]

Hi All,

I am a little stuck here. I want Qlik to count distinct month year from the list column based on user selection for any one, two or more States and out put a sum. I gave it some tries but seems like this is something out of my jurisdiction so please help!

For eg my out put for NY and CA combination should be 3 as Apr-20 and May-20 is common in both the rows for NY and CA so count them once. I am using that number in a formula in an expression so some expression code would be ideal. 

State	MonYr	MonthYear
NY	Apr-20	Apr-20, May-20, Jun-20
IL		May-20, Jun-20
DC	Apr-20	Apr-20
CA	Apr-20	Apr-20, Jun-20
TX		May-20, Jun-20
NY	May-20	Apr-20, May-20, Jun-20
IL	May-20	May-20, Jun-20
DC		Apr-20
CA		Apr-20, Jun-20
TX	May-20	May-20, Jun-20
NY	Jun-20	Apr-20, May-20, Jun-20
IL	Jun-20	May-20, Jun-20
DC		Apr-20
CA	Jun-20	Apr-20, Jun-20
TX	Jun-20	May-20, Jun-20

[rubenmarin · Accepted solution]

Hi, You can have another table splitting the values in rows, this can be done with Subfield:

OtherTable:
LOAD State,
  Subfield(MonthYear,',') as MonthYearCount
Resident OriginalTable;

Then you can just do a Count(distinct MonthYearCount) and it will count the different values od the states selected

[JonesBeach]

Hi I did so but it is counting the repeating values twice 

[rubenmarin]

HI, there must be something wrong, a count distinct doesn't count duplicates

[rubenmarin]

If you want to count distinct values overall, not by state, you can use TOTAL:

Count(TOTAL Distinct MonthYearCount)