Lost customers that returns

[gandalfgray]

Hi

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

[swuehl · Accepted solution]

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)

[gandalfgray]

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