Solved: Grouping customers based on their actions - Qlik Community

Report Inappropriate Content · ‎2014-10-29

Hi All,

I would like to be able to make arbitrary sets of customers who have performed some action, and be able to put them into a chart. Let me illustrate.

E.g. I want to be able to group my customers into those who have bought product A, product B, product C or None of the Above. Then I'd like to show my sales for each. Result should look something like:

Group Product A 1M

Group Product B 1.4M

Group Product C 2M

None of the Above 10M

The sets are not necessarily mutually exclusive, so a customer can be in Group Product A as well as Group Product B, if she happened to have bought both products.

The groupings should also take into account any selections, e.g. time period selected. So if a user chose July 2014, then the groupings should reflect those who bought a product in July 2014. The final aim is to be able to compare Lifetime Values based on customer behavior.

Thanks all.

mark_casselman · ‎2014-10-29

Hi,

A solution for 'row level formulas' is automatically generating all possible formulas and put them in a 'pick, match' formula.

Suppose you have 3 products: A, B and C, you should use straight table with the product as dimension and the following formula as expression:

pick(match(IslandProduct,'A','B','C'),sum({<Customer=P({<Product={'A'}>})>}Sales),sum({<Customer=P({<Product={'B'}>})>}Sales),sum({<Customer=P({<Product={'C'}>})>}Sales))

This should work for 3 products.

But, as you mentioned there are a lot more products. In this case you can generate the necessary formula by creating a variable vFormula with the following expression (including the single quotes when entering the formula) :

'pick(match(IslandProduct,'&concat(chr(39)&IslandProduct&chr(39),',')&'),'&concat('sum({<Customer=P({<Product={'&chr(39)&IslandProduct&chr(39)&'}>})>}Sales)',',')&')'

Then make the straight table with Product as dimension and =$(vFormula) as expression.

You get as result a table with the sum of sales of the customers that have the product of the row.

This solution generates a long formula, so thousands of products might not work or work slow, but a hundred products should work fine.

In attachement you will find a test dashboard with 3 products (just add more customers and products to test more products, I tested it with over 20 products).

Mark

View solution in original post

jyothish8807 · ‎2014-10-29

Hi Robert,

Try like this in straight chart:

In Dimension:

1. Take your Product as dimension.

2. aggr(Customer,Product)

exp:

Sum(sales)

Hope it works.

Regards

KC

Best Regards,
KC

Report Inappropriate Content · ‎2014-10-29

Hi,

You could create these groups in the script itself.

For eg: if you have Product and Customer in a table.

then while loading add a new field as below.

if(Product='A','Group A',if(Product='B','Group B',if(Product='C','Group C'))) as Group

Report Inappropriate Content · ‎2014-10-29

I don't think I can do that, because the groupings need to be arbitrary based on other selections.

jyothish8807 · ‎2014-10-29

Can you please share your app? That will be helpful.

Regards

KC

Best Regards,
KC

Report Inappropriate Content · ‎2014-10-29

I am trying your solution as described above. For some reason, it's not giving me the answers I expect, but it seems to be on the right track.

I'll create a sample application and share it which will illustrate the issue.

Anonymous · ‎2014-10-29

This would need to be applied to each expression, here's a sales example (total spend per customer - not just their spend on that product)

Sales For customers who have purchase Product A:

sum({$<Customer=P({$<Product={'Product A'}>})>}Sales)

Sales For customers who have purchase Product B:

sum({$<Customer=P({$<Product={'Product B'}>})>}Sales)

Report Inappropriate Content · ‎2014-10-29

The limitation with set analysis is that I have to explicitly set them up myself. In my example I put 3 groups but the reality is that there's thousands. Products are also added dynamically in the source system.

I was trying to get to a solution using the P() type of expressions but hit a brick wall because of this problem.

jyothish8807 · ‎2014-10-29

Hi Robert,

I guess it can be achieved by using pivot table:

PFA

Regards

KC

Best Regards,
KC

Report Inappropriate Content · ‎2014-10-29

Here's the model app