5

I've been scouring the net for over an hour and cannot find what I need.

I have two columns that contain a persons name; Contact and Created By. Both are in identical format.

Basically I need to count the distinct values of BOTH these columns combined. e.g. The name can be in each column of data multiple times but I only want the name counted once.

I tried using the below but it is returning a number higher than the actual distinct values between both columns. 

=Sum(Aggr(Count(Distinct [Created By]),[Contact]))

also tried this and am the same number is returned as the above.

=Count(Distinct [Contact] & [Created By])

Thanks in advance!

bdiamante
  • 15,980
  • 6
  • 40
  • 46
Mark
  • 327
  • 1
  • 7
  • 14

1 Answers1

4

I think you can try something like this:

count(distinct Contact) + count({$<[Created By]-=p(Contact)>} distinct [Created By])

Basically, it adds the count of uniques from Contact to the count of uniques from Created By where Created By isn't one of the names in Contact.

bdiamante
  • 15,980
  • 6
  • 40
  • 46
  • YOU ARE AWESOME!!! Thank you so much. I'm relatively new to Qlikview. Is it possible to add one more piece to it? Now that is working I've discovered I need to add where 'Created' is greater than or equal to 1/1/2016.Thoughts? – Mark Mar 08 '17 at 12:44
  • also what does the p in -=p stand for? – Mark Mar 08 '17 at 13:00
  • To save iterations this is exactly what I am looking to do now that I know that counting the distinct values between two columns is possible. I need to calculate the difference between the distinct count (contact+created by) at the 1st day of the year compared to current day/last day of the year, whichever is greater. e.g. 100 distinct at 1/1/2016, 125 distinct at 12/31/2016, value shown would equal 25. e.g. 150 at 1/1/2017, 166 at 3/8/2017, value shown would equal 16. – Mark Mar 08 '17 at 13:33
  • `p()` is a function that results in "the possible values" of the field passed. Its use in the above formula (without any set modifier) just results in all distinct values of `Contact`. The `p()` function has many wider uses. See [here](https://community.qlik.com/thread/113022) or try googling "Qlik P() and E()" for some more detailed info. – bdiamante Mar 08 '17 at 16:23
  • Assuming your data is set up so that if you were to select 1/1/16 in your calendar listbox that there would be associated names in `Contact` and `Created By`, you could modify the original formula like: `(count({$}distinct Contact) + count({$} distinct [Created By])) - (count({$}distinct Contact) + count({$} distinct [Created By]))`. If your data is set up so that 1/1/16 and 12/31/16 are cutoffs we'll have to modify this a bit. – bdiamante Mar 08 '17 at 16:39
  • Thank you for your response. So there is a Created date column (Created) which each line item will have populated. There will also always be a Contact and Created By populated. I have a multibox drop down where the user can select the year they want to view. If a previous year is selected the Created date should be from 1/1 to 12/31 of that year. If it is the current year it should be from 1/1 to the current date. Does that make sense? – Mark Mar 08 '17 at 17:00
  • Got it. I think I understand the setup. Try this: `(count({$}distinct Contact) + count({$} distinct [Created By])) - (count({$}distinct Contact) + count({$} distinct [Created By]))`. If a user selects a year in the interface, this will take the distinct name count for maximum `Created date` for that year and subtract the distinct name count for the minimum `Created date` of that year. – bdiamante Mar 08 '17 at 21:20
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/137594/discussion-between-bdiamante-and-user1590497). – bdiamante Mar 08 '17 at 21:24