Count/Find Unique Sets

[Anonymous]

Hello Qlik Community!

I am looking to retrieve a list of unique People IDs that have been visited by account managers.

I have three tables: Donors, Account Managers, and Contacts. The fields are like:

Donors:

Donor ID,

First Name,

Last Name,

Assigned Manager

AccountManagers:

Manager ID,

Manager First Name,

Manager Last Name (edited)

Contacts:

Contact ID,

Contact Type,

Contact Date,

Manager ID,

Donor ID

I'd like to be able to find out how many Visits (value in Contact Type) were made by an Account Manager to UNIQUE Donors. So if we have managers that are reporting a high quantity of visits, but they're all to the same 3 people, we want to tell them to branch out. I'm guessing this will use some type of Set Analysis or Aggr(), but I'm open really to any solutions.

Thanks for the help!

[sunny_talwar]

May be this

Dimension

Manager First Name

Manager Last Night

Expression

Count(DISTINCT [Donor ID])

[Anonymous]

Hi Sunny.

Can you elaborate a little more?

There are many different Activity Types: Visits, Phone Call, Email, Letter, etc.

How would I count the number of "Visits" an Account Manager does to Distinct Donors?

[Anonymous]

Here are some examples of the data:

Donors:

Load * Inline [

[Donor ID], [First Name], [Last Name], [Manager ID]

001,John,Smith,101

002,Jane,Doe,101

003,Albert,Einstein,202

004,Bill,Nye,202

005,Stephen,Hawking,303

006,Neil,Tyson,404

007,James,Bond,505];

AcountManager:

Load * Inline [

[Manager ID],[Manager First Name],[Manager Last Name]

101,Michael,Jordan

202,Lebron,James

303,Steph,Curry

404,Kevin,Durant

505,Larry,Bird

Contacts:

Load * Inline [

[Contact ID],[Contact Type],[Manager ID],[Donor ID]

9001,Visit,101,001

9002,Visit,101,002

9003,Phone,101,001

9004,Email,202,004

9005,Visit,202,003

9006,Visit,202,003

9007,Visit,202,003

9008,Visit,202,004

9009,Visit,303,005

9010,Email,303,005

9011,Phone,404,006

9012,Email,404,006

9013,Visit,505,007

9014,Phone,505,007

9015,Email,505,007

So the "Visit" Counts to Unique Donors should be as follows:

Manager ID	Number of Unique Donors Visited	Total Visits	Total Contacts
101	2	2	3
202	2	4	5
303	1	1	2
404	0	0	2
505	1	1	3

[sunny_talwar · Accepted solution]

Here you go....

Number of Unique Donors Visited

=Count(DISTINCT {<[Contact Type] = {'Visit'}>} [Donor ID])

Total Visits

=Count({<[Contact Type] = {'Visit'}>} [Contact ID])

Total Contacts

=Count([Contact ID])

[effinty2112]

Hi Weston,

I can't improve on Sunny's solution but can I make a suggestion about your data model? There's a synthetic table that I think highlights a little flaw in the logic. If the Manager ID field really belongs in the Donors table then, for the purposes of this exercise, it's not needed in the Contacts table too .

Maybe the Contacts table is the more natural home for Manager ID. If you remove this field from Donors we still get the correct result.

I think the Manager ID field is being overworked. It's not fair to ask it to tell us who the current manager is for a donor and also tell us who made each contact because over time contacts to a donor could be made by different managers. It might be that your database has more than one id field to meet these different requirements.

Good luck

Andrew

[Anonymous]

Totally agree about the model. I'm open to reworking it, but wasn't sure if there's an obvious way around it; I'm still learning about creating Link/Key tables in order to get everything to fit nicely from time to time. This is a solution I'll be working on though...

To your suggestion, the Manager ID belongs in it's own table. In theory, an Account Manager SHOULDN'T contact another Manager's Donors, but it happens from time to time by necessity. Do you have any suggestion on how to create a model where it works to get both assigned donors and contacted donors?

As far as the requirements go, it is necessary to be able to both tell who the ASSIGNED Manager is, as well as finding out who made the contact. One of the metrics we're trying to track is, basically, an overall view at ALL Contact Activity AND Contact Activity to only assignments. It's a pretty involved study, one I don't necessarily see all the value in, but it's a requirement nonetheless (I just do what I'm told!).

[Anonymous]

Sunny, as always, thank you!

You having provided yet another great solution. Cheers!