topic New customer analysis using set analysis in QlikView

New customer analysis using set analysis

rwunderlich — Mon, 13 Dec 2010 21:07:55 GMT

I'm working on the classic new/lost customer problem using set analysis. I've found a few examples on the forum, but they don't seem to fit exactly what I'm after. I'm using the AsOf table approach with a "MonthsBack" field. Attached is a simple example of where I'm getting stuck.

I expected that the set

{ <MonthsBack={'0'}> - <MonthsBack={'>0<4'}>}

would give me Customers who ordered in the current month and did not order in the prior three months. It's not working. Any idea where I'm going wrong?

Thanks,

Rob

New customer analysis using set analysis

johnw — Mon, 13 Dec 2010 21:44:03 GMT

Well, here's a quick alternative that works while I try to figure out what's going wrong with the set analysis:

Added Count = -sum(aggr(only(MonthsBack)=0,AsOfYearMonth,Customer))
Added = concat(aggr(if(only(MonthsBack)=0,Customer),AsOfYearMonth,Customer),',')

New customer analysis using set analysis

Oleg_Troyansky — Mon, 13 Dec 2010 21:56:26 GMT

I have a "gut feeling" that it doesn't work because the Set Analysis condition is being evaluated "globally", - e.g. once for the whole chart, and therefore cannot be sensitive to the individual "As of Month" values.

If you apply your Set Analysis condition "globally" (without any relation to AsOfMonthYear), you'll see that the only Customer and Date that remains in the set after subtracting one set from another, is Customer C1 with Order Date 5/15 - and that's I believe the reason you see an unexpected result in May.

On the other hand, I don't have a great solution for this problem - you probably have to go back to calculating flags in the load script, based on certain conditions, and using flags in combination with Set Analysis...

cheers,

New customer analysis using set analysis

johnw — Mon, 13 Dec 2010 22:02:30 GMT

You're exactly right, Oleg. I was just about to post that very explanation when I saw that you'd already posted it. 🙂

New customer analysis using set analysis

rwunderlich — Tue, 14 Dec 2010 00:23:48 GMT

Thanks to both of you. I understand the problem better.The aggr() solution helps. I'll see if I can work it into the complete problem.

-Rob

New customer analysis using set analysis

rwunderlich — Tue, 14 Dec 2010 19:32:43 GMT

Aggr() works for computing the Added, but how about Lost? Attached is an other example with everything but Lost. Any ideas?

Thanks,

Rob

New customer analysis using set analysis

johnw — Tue, 14 Dec 2010 20:16:14 GMT

I really should have just posted this yesterday. Here's an example I have with added, retained and lost customers, though obviously it isn't for your specific sample data. Hopefully you'll be able to adapt it to your own data, but if not, I can take a closer look later.

New customer analysis using set analysis

rwunderlich — Tue, 14 Dec 2010 21:08:46 GMT

Thanks John. That's a big help. I've now got something of a useful lost count by testing only(MonthsBack)=3. Still have some odd results but moving forward.

Thanks,
-Rob

New customer analysis using set analysis

johnw — Tue, 14 Dec 2010 21:22:59 GMT

Looking a little more closely, I'd think you'd want this for lost customers, assuming by lost customers you mean "customers I had last month but do not have this month". It seems to produce the results I'd expect.

concat(aggr(if(only({<MonthsBack={0,1}>} MonthsBack)=1,Customer),AsOfYearMonth,Customer),',')
count(aggr(if(only({<MonthsBack={0,1}>} MonthsBack)=1,Customer),AsOfYearMonth,Customer))

I switched to count() instead of -sum() because my first try with -sum() didn't work, count() just makes more sense, and it also more closely matches the concat(). Overall, it should be easier for maintenance.

New customer analysis using set analysis

rwunderlich — Tue, 14 Dec 2010 23:29:56 GMT

John,

In this app, the definition given for Lost is "Sale over previous 3 months but no sales for the past 3 months current month included.".

Which means customers I had 3 months ago but had no sales in the current month, the -1 month and the -2 month. So the only() technique works excellent, testing only the oldest month.

-sum(aggr(only(MonthsBack)=3, AsOfYearMonth,Customer))

Attached is my sample including the Lost. Thanks for the help.

-Rob

New customer analysis using set analysis

llauses243 — Wed, 15 Dec 2010 00:12:05 GMT

Hi,

This post is very singular, is refered to customers, is refered to set analysis, with partipation of 3 big referents.

Thanks Rob, John y Oleg.

Re: New customer analysis using set analysis

ecolomer — Mon, 15 Aug 2016 08:17:36 GMT

Yes is very very singular and very useful

Thank's for sharing

Saludos

Re: New customer analysis using set analysis

Anonymous — Tue, 20 Jun 2017 15:19:46 GMT

Yes, this is a great post, quite surprised there aren't many more New Customer analysis threads. This works well but takes an unsurprisingly long amount of time and consumes a large amount of resources when trying to calculate from a dataset with circa 10 million records over 15 months, and I will be required to expand this data set. Is this the most efficient way of doing this?

Re: New customer analysis using set analysis

jaymeknapp — Fri, 15 Mar 2019 13:30:47 GMT

How do I set/get the 'MonthsBack' and 'AsOfYearMonth' ?