Count #Orders for specific products, Determine segmentation by order timing

[samuel_lin]

Hello Qlik Geeks,

I have a data set like this:

CustomerID	Order Date	Product
123	10/1/16	A
123	10/1/16	B
123	10/3/16	C
123	10/5/16	D
123	10/10/16	A
456	10/1/16	A
456	10/2/16	A
456	10/3/16	B
456	10/5/16	C
456	10/5/16	D
456	10/10/16	C
456	10/11/16	C

I want to do something either in the backend script load or front end to:

(1) Segment customers by the number of times placing orders for specific or a group of product; or count the number of times a customer has ordered a specific product

(2) Determine the last date that a certain customer has ordered product A (or any other product)

(3) Segment customers into three segments:

     (a)customers who have recently (within the last 6months) bought productA

     (b)customers who have placed at least an order of productA 6+months ago and continue to buy other products other than A recently (within 6months)

     (c)customers who have placed at least an order of productA 6+months ago and stop buying.

I have tried to create segmentation from load script but the outcome doesn't seem accurate, please help and let me know your thoughts!

Thank you,

Samuel

[samuel_lin]

You're right. I used to apply if on products and order date using "and" and "or"...but the result from the segment isn't correct. I will do more research to see if I can get this solved.

Please let me know if you have any further hint.

Thank you!

[samuel_lin]

Hi Sunny, I am working on the third requirement applying your dimension code:

Aggr(

  If(Today() - Max({<[Product] = {'A', 'B'}>} [order date]) <= 185, Dual('Resent', 1),

  If(Today() - Max({<[Product] = {'A', 'B'}>} [order date]) > 185 and (today() - MAX([order date]))<=185, Dual('Ordered and Continued w/out A&B', 2),

  If(Today() - Max({<[Product] = {'A', 'B'}>} [order date]) > 185 and (today() - MAX([order date])) >185, Dual('Ordered and Lapsed', 3))))

, CustomerID)

The dimension is valid but for some reason, the second segment ("ordered and continued w/out A&B) seems wrong and not showing up. I would love your feedback please.

Thank you so much for your help!

Samuel