topic Re: Grouping customers based on their actions in QlikView

Grouping customers based on their actions

Wed, 29 Oct 2014 11:42:37 GMT

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.

Re: Grouping customers based on their actions

jyothish8807 — Wed, 29 Oct 2014 11:50:26 GMT

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

Re: Grouping customers based on their actions

Wed, 29 Oct 2014 11:55:18 GMT

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

Re: Grouping customers based on their actions

Wed, 29 Oct 2014 16:44:31 GMT

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

Re: Grouping customers based on their actions

jyothish8807 — Wed, 29 Oct 2014 16:46:52 GMT

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

Regards

Re: Grouping customers based on their actions

Wed, 29 Oct 2014 16:49:26 GMT

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.

Re: Grouping customers based on their actions

Anonymous — Wed, 29 Oct 2014 16:57:33 GMT

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)

Re: Grouping customers based on their actions

Wed, 29 Oct 2014 17:05:47 GMT

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.

Re: Re: Grouping customers based on their actions

jyothish8807 — Wed, 29 Oct 2014 17:14:04 GMT

Hi Robert,

I guess it can be achieved by using pivot table:

PFA

Regards

Re: Re: Grouping customers based on their actions

Wed, 29 Oct 2014 17:24:45 GMT

Here's the model app

Re: Re: Grouping customers based on their actions

Anonymous — Wed, 29 Oct 2014 18:35:13 GMT

Does your full data model include invoice ID or something that designates a total invoice?

If so, please repost with that included. I may have a solution.

Re: Re: Grouping customers based on their actions

mark_casselman — Wed, 29 Oct 2014 20:00:47 GMT

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

Re: Grouping customers based on their actions

jyothish8807 — Thu, 30 Oct 2014 03:15:48 GMT

Hi Robert,

Why dont you use pivot chart?You can acheive it pretty easily.

Regards

Re: Re: Grouping customers based on their actions

Thu, 30 Oct 2014 09:27:32 GMT

No it doesn't. The actual data model is aggregated so transaction level information is thrown away.

Re: Grouping customers based on their actions

Thu, 30 Oct 2014 09:30:21 GMT

A pivot chart will not give me the results I need.

The results are sort of generated in 2 steps:

1) Get those customers who bought product A (or took promotion 1 or whatever).

2) What was their revenue?

A customer can have bought product A as well as product B. In that case, he has to appear in both sets. A pivot chart will only tell me what the total sales on product A are no?

Re: Grouping customers based on their actions

Thu, 30 Oct 2014 14:32:10 GMT

I'm having a look at your solution right now. It seems like this is the right one.

Re: Grouping customers based on their actions

Fri, 31 Oct 2014 14:34:30 GMT

This worked! Thanks.

Performance suffers when no selections on product are made. But when the user selects a few products, it's great.