Distinct counts with condition

[jw8s]

Hi. This is my source data

Person	Registration_date	Activity
X	01.01.2023	A
X	01.01.2023	B
X	01.01.2023	B
X	01.01.2023	C
Y	01.01.2023	A
Y	01.01.2023	B
Z	01.01.2023	A
Z	01.01.2023	C
W	02.01.2023	A
W	02.01.2023	D

I need to create a pivot table where I have Registration_date in rows, Activity in columns and an expression that counts unique persons for every registration date in a way that it does not take into account which activity was performed (meaning that all counts in a row should be the same for every column). See below

	A	B	C	D
01.01.2023	3	3	3	3
02.01.2023	1	1	1	1

Can you please help me with this?

[Vegar]

Use the  measure  

=count([Person])

to calculate the no of persons.

I notice that person X performed activity B twice on the same day. If you only want to calculate it once then you can use the measure 

=count(Distinct [Person])

[jw8s]

Unfortunately =count(Distinct [Person]) would result in the following table where there is a different value for each activity

	A	B	C	D
01.01.2023	3	2	2	0
02.01.2023	1	0	0	1

I would like to get the same value in every column where it represents the number of persons in the respective registration date no matter which activity was performed. 

[HeshamKhja1]

@jw8s , just our of curiosity, if all columns will have the same values, why do you need multiple columns? Won't one column give the insight?

[jw8s]

My calculation consists of multiple parts and part of it is division. I was able to successfully write the other parts but I am struggling with this.

[HeshamKhja1 · Accepted solution]

You can use a combination of TOTAL and Distinct. Check expression below:

Count(TOTAL <Registration_date> distinct Person)

The Total <d1>  tells Qlik to get total based on d1.

I think you are asking a similar question to: https://community.qlik.com/t5/QlikView-App-Dev/Ignore-Dimension-in-calculation-Pivot-table/td-p/1128...

[jw8s]

@HeshamKhja1 lets say I had an additional flag column in my dataset. How can I add a condition {<flag={'TRUE'}>}? I tried Count(TOTAL {< flag={'TRUE'}, Registration_date >} distinct Person) but that did not do the trick

[HeshamKhja1]

Maybe like this:

Count(TOTAL <Registration_date> distinct {<flag = {'TRUE'}>} Person)