
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Qlik Sense Pivot Table - Count Distinct question
I am working on the Pivot table in Qlik Sense which have both AppIDs and CustIDs but some of the entries do not have a CustID. I wish to count AppIDs values only one time in each measure of the Pivot Table. Most of the time this is working but on several entries where the CustID field in blank, that AppID value (even though it is not a duplicate) is not being counted.
I thought using COUNT(DISTINCT AppID) should work but it is not counting the AppIDs that don't have a value in the CustID field.
What am I doing wrong? Please help.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Tim,
Thank you so much for your help.
I have figured out what I did wrong. In order to avoid NULL state codes, I eliminated them in that field. Changed it to no longer eliminate the NULL values in the State field and it brought back in the missing values.
It was your mention of another field that might be effecting the totals that made it investigate.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Would you be able to share an image to show what you are getting and what is exactly you are looking to get?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The highlighted totals below are not correct when compared to counting distinct AppID within the downloaded excel detail document of the same information. After research, I noticed that if the values under CustID are blank then the AppID is not being counted even though they are not a duplicate.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Could you possible give a screenshot of the entire table, a bit more info on how the data is structured? It's very difficult to figure out what's going on when we just see a few numbers "that aren't correct".

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Measures being used are: Count(Distinct AppID) and Count(Distinct CustID)?
Are you certain that when a CustID is missing, there aren't any other fields missing as well that could possible cause the issue?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
After removing duplicate ApplicationIDs from the excel document created from the data available, you get a total of 13100. (see below)
In the 2nd pic below, you will see that 3332 rows do not include the CustomerID. 13100 - 3332 = 9768 which is the total number of Web Views you get in the Pivot table above.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Are you filtering on the category Webviews in the Excel as well?
In the first picture, your AppID seems to be sorted low to high, but it's missing all the numbers that you can see in your second picture. Are you certain those AppID in the second picture are supposed to belong to the category Webviews?
If it's possible, sharing example data would be helpful.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Tim,
Thank you so much for your help.
I have figured out what I did wrong. In order to avoid NULL state codes, I eliminated them in that field. Changed it to no longer eliminate the NULL values in the State field and it brought back in the missing values.
It was your mention of another field that might be effecting the totals that made it investigate.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Glad I could help!
