10 Replies Latest reply: Jul 3, 2014 2:34 AM by Eugen Heier

Set Analysis - Greater then Date

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

• Re: Set Analysis - Greater then Date

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.

• Re: Set Analysis - Greater then Date

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?

• Re: Set Analysis - Greater then Date

better if you provide sample apps or sample data file.

• Re: Set Analysis - Greater then Date

I try to create a sample Data file.

At the moment I can write the answer as SQL:

Select Count(distinct a.order_id)

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;

• Re: Set Analysis - Greater then Date

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

• Re: Set Analysis - Greater then Date

See:

• Re: Re: Set Analysis - Greater then Date

Ok, here is a sample file.

Thanks for your time.

• Re: Set Analysis - Greater then Date

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)

• Re: Set Analysis - Greater then Date

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

• Re: Set Analysis - Greater then Date

Is there any way to depict  " order_date> P () " in Set Analysi?