List of clients not using a specific product

[luiscuderi]

I'm trying to find a solution for the identification of the clients that are NOT using a product in, say, current year.

I have a registry of all the registered clients, with personal data associated, it contains 40K records.

I have a registry of products, containing 1K records, and i have a sales table with all sales over the last 4 years with detail of months, and keys to the client registry and product registry.

I can easily find the clients using product1 in march 2018.

What i can't  do right now is to identify the clients NOT using Product1 in march 2018.

Reading similar discussions i've found that i need to create the missing records in my sales table in order to be able to achieve my goal. But this would mean creating a table with 40K (clients) x 1K (products) x 4 (years) x 12 (months) = 1.92 billion records!!!!

Is there a more efficient way?

Such a size would generate problems to my application?

[JordyWegman]

Hi Luis,

It sounds like you would like to use the associative engine of Qlik. Try to create two filter panes, one with Product and another one with Clients.

Select Product1 and you will see in the filter pane of the Clients two colors, light gray, which are all the customers who have a relation with Product1 and dark gray, all the customers that don't have a relation with Product1.

This will give you the result of all Clients that who are not using Product1.

Jordy

Climber

[luiscuderi]

Hi Jordy,

thank you for your reply.

I've tryed it and it works. Now my questions is: can i export the list of the clients that are not using Product1?

can i create graphs with, say, geographic location of such clients?

[JordyWegman]

Hi Luis,

Yes that is possible, you have multiple ways, but one would be to create a map with all your customers. Do the same selection with Product1 and then select all the 'dark gray' clients. On your map you will then have all your Clients that are not related. You can also select all clients that are related and then 'select excluded'.

Jordy

Climber

[luiscuderi]

Thank you Jordy.

I've just tryed your solution, it can make me export the list of clients and also to see them on a MAP.

The select excluded seems to work. I still have a problem when i use more than one filter. 

If i add one more filter, say Male customers, in the exluded list i end up to have not only the clients not using product1, but also all the female clients.

[JordyWegman]

Hi Luis,

Also this is possible, but you need to be careful in which order you make the selections. Try the following, in this order, first select Male, then Product1 and then all 'Dark Gray' Clients.

Jordy

Climber

[luiscuderi]

Hi Jordy,

thank you, it works great! even with complex selections, in the right order.

In this way i solved the problem of identifying the clients and exporting them.

I wonder if there's a way to create measures on that. I mean, for instance, a measure of the number of male clients of area 1 that are not using product1. 

This would be the complete answer to my needs.

[JordyWegman · Accepted solution]

Hi Luis,

That would mean that you will need set analysis. And the good thing is that Qlik made a special function for this.

Create all your selections again, make a measure and enter Count(Distinct Clients). Click between distinct and Clients and insert your selections as set analysis. See my example:

Jordy

Climber

[luiscuderi]

Thank you Jordy.

Thats the solution. I can't find that function in my version, i'm using Qlik Sense 12.26.1

I'll investigate about the update.

Thanks again

Luigi