3 Replies Latest reply on Dec 14, 2017 2:19 PM by Okechukwu Ossai

Customers who ordered items in both categories in both periods

Hi,

I've found solutions to "who purchased both A and B" but it doesn't seem to fit my scenario.

I have a list of products (15,000 of them) and need to figure out what customers ordered a product in both order periods - Period A or Period B

ProductID           Customer                 Order Period

A                    B

124888-001         Smith                YES              YES

122335-020        Hart                   YES               NO

122556-009        Hart                   YES               YES

So my results to the above scenario should be

124888-001  Smith              YES

122556-009  Hart                YES

with the 2nd ProductID thrown out.

I'm trying out PREVIOUS_ROW with LOOKUP but it isn't giving me a TRUE results for both Order Periods.

IF ATTR([Customer]) = LOOKUP(ATTR([Customer]),-1) THEN IF ATTR([ORDER PERIOD]) != LOOKUP(ATTR([ORDER PERIOD]),-1) THEN "TRUE" ELSE "FALSE"

END

END

Am I close???

Toni

• 1. Re: Customers who ordered items in both categories in both periods

Hi Toni,

It depends on how your data is structured. I assume your data is in the format below. Could you please confirm or better still post a sample data in an excel spreadsheet?

 ProductID Customer Order Period Made Order? 124888-001 Smith A YES 124888-001 Smith B YES 122335-020 Hart A YES 122335-020 Hart B NO 122556-009 Hart A YES 122556-009 Hart B YES

Ossai

• 2. Re: Customers who ordered items in both categories in both periods

Hi Ossai,

Yes, what you've show is the raw data format I have, thank you.

• 3. Re: Customers who ordered items in both categories in both periods

Hi Toni,

You can use an LOD, like the one below

[Ordered in Both Periods?]

{FIXED [Product ID], [Customer]: MIN([Made Order?])}

Put this calculated field on the filter shelf and select 'YES'. See attached workbook in version 10.0

Regards,

Ossai