Distinct Count of ID when ID field is taken from multiple tables

[laurafinn]

Hi all,

Can anybody help?

I have the below expression that correctly counts the distinct IDs within each group to give me a total:

Count({<[CaseContact.PwMND Filter] = {'PwMND'}>} distinct [CaseContact.ID])
+ Count({<[Support.Activity Type] = {'Support Activity'}, [SupportContact.PwMND Filter] = {'PwMND'}>} distinct [SupportContact.ID])
+ Count({<[ActivitiesContact.PwMND Filter]= {'PwMND'}>} distinct [ActivitiesContact.ID])
+ Count({<[Loans.KPI Reportable] = {'Yes'}>} [LoansContact.ID])
+ Count({<[MembershipsContact.PwMND Filter] = {'PwMND'}>} distinct [MembershipsContact.ID])
+ Count( distinct ID)
+ Count({<[GrantContact.PwMND Filter] = {'PwMND'}>} distinct [GrantContact.ID])
+ Count({<[CampaignsContact.PwMND Filter] = {'PwMND'}>} distinct [CampaignsContact.ID])

However

The ID Filed contains a Client ID, from this expression as a whole, I'd like to know how many distinct IDs there are, not just how many are distinct per group, is there something I can add to the expression to count distinct on the whole population of IDs?

[chrismarlow]

Hi,

I think you can use P() function to stop including values possible in the other ID fields, although even cutting the problem down a bit it starts to get long winded very quickly;

Cheers,

Chris.

[ArnadoSandoval · Accepted solution]

Hi @laurafinn 

I found the description of your problem fascinating because the type of SET analysis it requires, I read @chrismarlow suggestion with the P() function, but I found it to be complex for my own taste, so I read careful your problem, and I believe that I understood what you are trying to do, now this is my understanding:

Understanding:

1.  The expression you posted perform a Count(Distinct across 7 different groups.
2. I will refer to them as SET, because Groups and SETs are pretty close the same thing, and these groups are defined by set expressions.
3. These groups are: CaseContact, SupportContact,  ActivitiesContact, LoansContact, MembershipsContact, GrantContact, CampaignsContact and ID
4. With these groups I prepared an Excel file with sample data illustrating your data in order to replicate your problem; I attached the Excel file to this reply, its name is: Count_Distinct.xlsx
5. The Excel file has 3 records, with data for these 8 groups, I was careful to assign ID = 1 to all the groups on the first record, the second record satisfy the rule of each individual group while using a different ID, and the third record, also with different IDs does not satisfy the groups' rule, therefor the Count for each group will be 2, and the ID group with a count of 3.
6. I also draw a diagram trying to illustrate the data, and the expected result.

The highlighted areas are each group (SET) id elements, featuring the ID = 1 shared between them, this mean that there are 10 unique IDs, which is your expected result, while your current formula returned 17 IDs, which is not the expected result.

Analysis of each Group:

Your first Count expressions is like this one:

Count({<[CaseContact.PwMND Filter] = {'PwMND'}>} distinct [CaseContact.ID])

Its SET expression defines the following set of values:

1, 2

Your second Count expression is like:

Count({<[Support.Activity Type] = {'Support Activity'}, [SupportContact.PwMND Filter] = {'PwMND'}>} distinct [SupportContact.ID])

Its SET expression return these values:

1, 4

With the first two Count/Set expression we get 3 different IDs.

Our problem:

Overall, each Count function define a Set expression, our problem is to grab each one of them in one single SET for the 7 different groups, it is like Concatenating each Set returns into a single one, this is possible as explained in this reply.

Required Functions:

These are the functions required to implement this solution:

• Concat - chart function This function takes a SET expression parameter and this is how we will grab each Count's SET expression.
• ValueList - chart function This function allows as to convert into a SET the result returned by the Concat function.

Implementation:

The following screenshot are taken from the application written to implement and test the solution, it contains different stages of the process, as I did not write the final expression in one go, it was done by stages; The attached QVF file, Z_CountDistinct-01.qvf contains a demo solution.

NOTE: When I implemented this application I got rid of dots and spaces on the column names, as they usually force using square brackets in the field names.

I try as much as possible to use variables when implementing complex or long expressions in Qlik Sense; the screen shot below show the variables implemented in the attached solution.

You may notice that I created two variables per group (well, for the first four groups); Set_Group and Set_Group_Text, the only difference between them is the second one, with the suffix _Text are used in the UI's text components, while the ones without the _Text suffix are the ones used in the KPI components.

This is the expression for the variable: Set_Activities

=chr(39) & Concat({<[ActivitiesContact_PwMND_Filter]= {'PwMND'}>} distinct [ActivitiesContact_ID], chr(39) & ',' & chr(39)) & chr(39)

I consume this variable in the KPI components for activities, as shown:

Count(Distinct ValueList($(Set_Activities)))

Notice that this is the place we implement the function ValueList.

Now, the text component showing the detailed group of activities elements consume the Set_Activities_Text variable

$(Set_Activities_Text)

Set_All, Set_All_Text and KPI expression:

Set_All: (warning, I slightly changed your fields names)

= chr(39) & Concat({<[CaseContact_PwMND_Filter] = {'PwMND'}>} distinct [CaseContact_ID], chr(39) & ',' & chr(39)) & chr(39) & ','
& chr(39) & Concat({<[Support_Activity_Type] = {'Support Activity'}, [SupportContact.PwMND Filter] = {'PwMND'}>} distinct [SupportContact_ID], chr(39) & ',' & chr(39)) & chr(39) & ','
& chr(39) & Concat({<[ActivitiesContact_PwMND_Filter]= {'PwMND'}>} distinct [ActivitiesContact_ID], chr(39) & ',' & chr(39)) & chr(39) & ','
& chr(39) & Concat({<[Loans_KPI_Reportable] = {'Yes'}>} [LoansContact_ID], chr(39) & ',' & chr(39)) & chr(39) & ','
& chr(39) & Concat({<[MembershipsContact_PwMND_Filter] = {'PwMND'}>} distinct [MembershipsContact_ID], chr(39) & ',' & chr(39)) & chr(39) & ','
& chr(39) & Concat( distinct ID, chr(39) & ',' & chr(39)) & chr(39) & ','
& chr(39) & Concat({<[GrantContact_PwMND_Filter] = {'PwMND'}>} distinct [GrantContact_ID], chr(39) & ',' & chr(39)) & chr(39) & ','
& chr(39) & Concat({<[CampaignsContact_PwMND_Filter] = {'PwMND'}>} distinct [CampaignsContact_ID], chr(39) & ',' & chr(39)) & chr(39)

 KPI expression:

Count(ValueList($(Set_All)))

Set_All_Text:

chr(39) & Concat({<[CaseContact_PwMND_Filter] = {'PwMND'}>} distinct [CaseContact_ID], chr(39) & ',' & chr(39)) & chr(39) & ','
& chr(39) & Concat({<[Support_Activity_Type] = {'Support Activity'}, [SupportContact.PwMND Filter] = {'PwMND'}>} distinct [SupportContact_ID], chr(39) & ',' & chr(39)) & chr(39) & ','
& chr(39) & Concat({<[ActivitiesContact_PwMND_Filter]= {'PwMND'}>} distinct [ActivitiesContact_ID], chr(39) & ',' & chr(39)) & chr(39) & ','
& chr(39) & Concat({<[Loans_KPI_Reportable] = {'Yes'}>} [LoansContact_ID], chr(39) & ',' & chr(39)) & chr(39) & ','
& chr(39) & Concat({<[MembershipsContact_PwMND_Filter] = {'PwMND'}>} distinct [MembershipsContact_ID], chr(39) & ',' & chr(39)) & chr(39) & ','
& chr(39) & Concat( distinct ID, chr(39) & ',' & chr(39)) & chr(39) & ','
& chr(39) & Concat({<[GrantContact_PwMND_Filter] = {'PwMND'}>} distinct [GrantContact_ID], chr(39) & ',' & chr(39)) & chr(39) & ','
& chr(39) & Concat({<[CampaignsContact_PwMND_Filter] = {'PwMND'}>} distinct [CampaignsContact_ID], chr(39) & ',' & chr(39)) & chr(39)

Comments:

I tried to replace chr(39) & ',' & chr(39) with a variable, but it did not work.

Please review this reply and then ask any question you may have.

Hope this helps,

[ArnadoSandoval]

Hi @laurafinn 

I forgot to attach the Excel file!

[laurafinn]

Arnando,

Thank you!  This is amazing 🙂 So well written and thorough, I've managed to recreate exactly what you did and have validated the results as correct!

The only other slight complication is that I want to be able to plot the distinct count in each month.  The above 7 tables that the Set_all variable refers to are joined to a master calendar with a "reporting" date.  When I add the set analysis to my pivot, with "Reporting Year Month as the dimension"  it provides the distinct count across the whole data set rather than by month.  You've spent enough time here but I'm happy to investigate the potential solution if you agree my approach should be along the lines of adding month detail to the "set_all" variable?

I suppose I just want to check it's possible before I disappear down that rabbit hole.

Thanks again for your help with this.

Laura

[ArnadoSandoval]

Hi @laurafinn 

Indeed, I have a lot of fun working with your problem, adding a monthly summary is interesting as well, I will try to do that with the solution I shared in this thread, I will post an update if I succeed.

Regards,