topic Set Analysis - Greater then Date in QlikView

Set Analysis - Greater then Date

Tue, 01 Jul 2014 08:26:24 GMT

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

tresB — Tue, 01 Jul 2014 08:33:06 GMT

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

MK_QSL — Tue, 01 Jul 2014 08:43:29 GMT

better if you provide sample apps or sample data file.

Re: Set Analysis - Greater then Date

Tue, 01 Jul 2014 08:46:26 GMT

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

Tue, 01 Jul 2014 08:54:27 GMT

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;

Re: Set Analysis - Greater then Date

Tue, 01 Jul 2014 09:12:19 GMT

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

tresB — Tue, 01 Jul 2014 09:18:16 GMT

See:

Re: Set Analysis - Greater then Date

Anonymous — Tue, 01 Jul 2014 09:27:09 GMT

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

Tue, 01 Jul 2014 09:39:34 GMT

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

Re: Re: Set Analysis - Greater then Date

Wed, 02 Jul 2014 08:06:41 GMT

Ok, here is a sample file.

Thanks for your time.

Re: Set Analysis - Greater then Date

Thu, 03 Jul 2014 06:34:03 GMT

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