Set Analysis - Greater then Date

[#1]

Hi all,

I have the following problem. I must determine the number of (following) orders from customers who have purchased a particular (selected) product in the first order.

At the moment I have a feeling that my Set Analysis formula ignored the part with order_date.

Count({1<customer_id=P({<first_order={'Y'},product_id=P(product_id)>} customer_id),order_date={">$(=Date({1<first_order={'Y'},product_id=P(product_id)>} order_date))"}>} DISTINCT order_id)

Can you please look at you the formula.

Thanks

[tresesco]

May be a bit simpler like this?

Count({1<product_id=p(product_id), first_order={'Y'},order_date={">$(=Only(order_date)"}>} DISTINCT order_id)

Not sure though.

[MK_QSL]

better if you provide sample apps or sample data file.

[#4]

The mark "first_order" is associated with an order number. If I do so as you suggested, then only orders with "first_order = Y" is evaluated. Or I'm wrong?

[#5]

I try to create a sample Data file.

At the moment I can write the answer as SQL:

Select Count(distinct a.order_id)

from order_headers a

join (Select a.customer_id,a.order_id,a.order_date from order_headers a

      join order_positions b

      on a.order_id = b.order_id

      where b._product_id = '4711' and a.first_order = 'Y') b

on a.customer_id = b.customer_id

where a.order_date > b.order_date;

[#6]

Is the command: order_date = {"> $ (= Only (order_date))"} retrieved only a single order date or individually for each row?

[tresesco]

See:

[Anonymous]

Hi,

Adding Product field in expression may not make sense if you have selection for product. The order date should be only one at a time, If the customer has made order few times than you have to use Min or Max function in the expression. Below expression may work in your environment.

PS: I didn't test this script, there might be some typo error.

=Count({<customer_id=P({1<first_order={'Y'}>} customer_id), order_date={">$(=Max(P({1<first_order={'Y'}>} order_date)))"}>} DISTINCT order_id) 

[#9]

I added product=P(product_id) because the setting {1<... cancels the "product_id" selection again.

[#10]

Ok, here is a sample file.

Thanks for your time.