
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Distinct counts with condition
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?
- Tags:
- count
- expression
- pivot
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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])
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe like this:
Count(TOTAL <Registration_date> distinct {<flag = {'TRUE'}>} Person)
