Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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