topic Lost customers that returns in QlikView

Lost customers that returns

gandalfgray — Thu, 23 Aug 2012 06:36:15 GMT

I would like to do the following:

Show the customers which bought product A in period 1 and did not buy product A in period 2, and bought product A in period 3.

How can that be done in ver 10.

Is it easier in ver 11, and in that case why?

/gg

Re: Lost customers that returns

swuehl — Thu, 23 Aug 2012 09:17:14 GMT

So your data may look like this?

LOAD * INLINE [

Customer, Period, Sales

A, 1, 10

A, 2, 20

A, 3, 30

B, 1, 10

B, 3, 20

C, 1, 30

C, 2, 20

D, 2, 10

D, 3, 05

E, 1, 10

F, 2, 10

G, 4, 10

];

Then this should work as e.g. a field expression in a list box in versions 10 and 11 (not sure if there is a better approach in QV11):

=aggr(

only(

{<Customer = e({<Period={2}>})*{"=sum({<Period = {1}>}Sales) and sum({<Period = {3}>}Sales)"} >}

Customer)

,Customer)

Re: Lost customers that returns

gandalfgray — Thu, 23 Aug 2012 10:07:04 GMT

Hi swuehl

thanks for the reply.

Your expression works,

and I found an alternative that also gives the same results:

=only({<Customer = e({<Period={2}>})*p({<Period={1}>})*p({<Period={3}>})>} Customer)

Great so far!

But the periods 1, 2, 3 should be selectable by the end user,

and I did it this way:

Facts:

LOAD * INLINE [

Customer, Period, Sales

A, 1, 10

A, 2, 20

A, 3, 30

B, 1, 10

B, 3, 20

C, 1, 30

C, 2, 20

D, 2, 10

D, 3, 05

E, 1, 10

F, 2, 10

G, 4, 10

H, 1, 10

H, 3, 20

];

P2:

LOAD Distinct Period As P2

Resident Facts;

P3:

LOAD P2 As P3

Resident P2;

And the expression:

=only({<Customer = e({<Period=P2>})*p({<Period=P3>})>}Customer)

Selecting in Period, P2 and P3 now gets my desired results