Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
rwunderlich
Luminary Alumni
Luminary Alumni

New customer analysis using set analysis

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



1 Solution

Accepted Solutions
johnw
MVP
MVP

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.

View solution in original post

13 Replies
johnw
MVP
MVP

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),',')

Oleg_Troyansky
Luminary Alumni
Luminary Alumni

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,

johnw
MVP
MVP

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

rwunderlich
Luminary Alumni
Luminary Alumni
Author

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

rwunderlich
Luminary Alumni
Luminary Alumni
Author

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

Thanks,

Rob

johnw
MVP
MVP

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.

View solution in original post

rwunderlich
Luminary Alumni
Luminary Alumni
Author

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

johnw
MVP
MVP

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.

rwunderlich
Luminary Alumni
Luminary Alumni
Author

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