Is this possible to visualize in Qlik?

[el-WIN]

Hi all,

I am a total newbie to Qlik exploring what it can do and what it can't. I have a requirement that hopefully Qlik can meet. 

Requirement: show me what percent of partners have registration numbers above 60 percent.

Data:

Partners table

id	name	created	lastmodified
1	BL Team	2017-09-28 14:58:33+00	2017-09-28 18:58:33+00
2	BL Contractor	2017-09-28 14:58:33+00	2017-09-28 18:58:33+00
3	BL test	2017-09-28 14:58:33+00	2017-09-28 18:58:33+00

Cohorts table:

id	name	partner_id
1	BL Cohort1	1
2	BL Cohort2	2
3	BL Cohort3	3

Participants table:  (registration_step = 8 means registration completed.)

id	user_id	cohort_id	registration_step
1	1028319	1	8
2	849194	1	3
3	1028984	1	8
4	1028985	2	8
5	1028986	2	8
6	1028987	2	8
7	1028988	3	4
8	1028989	3	4
9	1028990	3	8

As you can see participants table is connected to cohorts table, and cohorts table is connected to partners table. 

Now I want to see what percent of partners have all their participants complete registration (registration_step = 8).  I can see that there are 3 participants in cohort_id=1, hence in partner_id =1.  Two of them have completed registration and 1 hasn't. So the registration rate for partner 1 is 66 percent. 

Registration rate for partner 2 is 100.

Registration rate for partner 3 is 33.

So 2 out of 3 (or 66 percent) partners have registration rate > 60.

How could I calculate and visualize this in Qlik? I understand it may require creating custom fields, but don't know what those could be.

[marcus_sommer]

You could use just a table with the partner- respectively cohort-name and an expression like:

count({< registration_step = {8}>} user_id) / count(user_id)

to get the wanted percent and then may wrap it within a condition if it's bigger/lesser as your threshold.

To make this work you need also to rename your fields properly because all identical fieldnames will be associated. This means the id from the partner table must become the partner_id and the id from the cohort must become the cohort_id. Further you need to name the fields "name" differently.

- Marcus

[el-WIN]

Hi Marcus,

Thanks for your response. If I count the way you describe then I am counting what percent of users have completed registration. That doesn't brake them down by partners though. The reports that my stakeholder wants to see is the partner level reports.  He wants to be able to log in and see how the partnerships are doing? What percent of partnerships have their users registration completion above 85 percent. From there they want to be able to drill down and see which partners are beyond, and further who the users are that haven't completed registration yet.

The filed properties are not identical in real DB. I just made mistake when naming them in my example table. 

[marcus_sommer]

Have you tried my suggestion? What didn't work or is different to your expectation?

- Marcus

[el-WIN]

Perhaps I don't understand your solution, but I think registration rate of users and partners are different. In the original example I set the threshold to 60%. Let's change it to be 85% so that the new requirement is to show what percent of partners have registration numbers > 85 percent.

If I use count({< registration_step = {8}>} user_id) / count(user_id) I get 66 percent (6/9=0.66) 

There are 3 participants in partner_id =1. Two of them have completed registration and 1 hasn't. So the registration rate for partner_1 is 66 percent.

There are 3 participants in partner_id =2. All of them have completed registration. So the registration rate for partner_1 is 100 percent.

There are 3 participants in partner_id =3. One of them have completed registration and 2 haven't. So the registration rate for partner_1 is 33 percent.

Now, the total number of partners with registration rate >85 is one. This means that 1/3 or 33% of partners meet my criterion.  This is different than 66 that I get if I run count({< registration_step = {8}>} user_id) / count(user_id)

[marcus_sommer]

You may just change your threshold-value maybe through a variable and further aggregating the result of the condition, for example with something like:

sum(aggr(-(count({< registration_step = {8}>} user_id) / count(user_id) > $(var)), cohort_id))

- Marcus