Qlik Sense Count Aggregate

[Ipsita20191]

Hello Guys,

I need one help regarding the below mentioned situation, I am pretty stuck here.

I am using

= if(aggr (count (distinct Status),[Person ID]) = 1 ,Status,'Dual')

Current Table
Person ID	Date	Status
Per1	1/1/2022	A
Per1	2/1/2022	B
Per1	5/1/2022	A
Filter selection
1/1/2022
Person ID	Date	Status	New Status
Per1	1/1/2022	A	A
Filter selection
both(1/1/2022 and 2/1/2022)
Person ID	Date	Status	New Status
Per1	1/1/2022	A	Dual
Per1	2/1/2022	B	Dual
Filter selection
both(2/1/2022 and 5/1/2022)
Person ID	Date	Status	New Status
Per1	2/1/2022	B	Dual
Per1	5/1/2022	A	Dual
Filter selection
both(1/1/2022 and 5/1/2022)
Person ID	Date	Status	New Status
Per1	1/1/2022	A	A
Per1	5/1/2022	A	A
New Status will check what is the distinct count of column Status per Person
If Distinct count Status column is 2 that person then Dual , Otherwise put the Status column Value

[BrunPierre · Accepted solution]

Hope it works for you.

New status expression

IF(COUNT(AGGR(COUNT(DISTINCT Status),[Person ID],Status))='2','Dual',Status)

Output

[Ipsita20191]

it Worked.Thanks.

It will be used as measure.

If we want to use as dimension i think we can use this one.

aggr(if(count(distinct Status) >1 ,'Dual',Status),[Person ID])

[Ipsita20191 · Accepted solution]

It is Working. Thanks. We can use this as measure.

If we want to use this as dimension, this will work

aggr(if(count(distinct Status) >1 ,'Dual',Status),[Person ID])