topic Re: Re: Using min function in target field in set analysis in QlikView

Using min function in target field in set analysis

Tue, 10 Jun 2014 11:55:07 GMT

I have the following table :

CUST_USER_ID

PURCHASE_EVENT_DT

04/5/2014

05/5/2014

07/5/2014

04/5/2014

10/5/2014

I want to calculate the number of buyers who do their first purchase the date that qlikVIew users select through a calendar object.

For example,

In 04/5/2014, the total number of new buyers are 2 ( cust_user_id: 1 , 3 )

In 05/5/2014, the total number of new buyers is 0

In 10/5/2014, the total number of new buyers is 1 ( cust_user_id: 2 )

I try to do it using the following set analysis :

Count({<min(PURCHASE_EVENT_DT)={$(vSelectedDate)}>} distinct CUST_USER_ID)

But there is an error in set analysis. Can you help me please?

Re: Using min function in target field in set analysis

rogerioqv — Tue, 10 Jun 2014 12:01:25 GMT

Try:

Count({<PURCHASE_EVENT_DT={"=$(vSelectedDate)"}>} distinct CUST_USER_ID)

Do not forget that the dates have to be in the same formats.

Re: Using min function in target field in set analysis

alexandros17 — Tue, 10 Jun 2014 12:02:53 GMT

You cannot use min (or any other function) in the left part of the expression, I think that if the use selects a date the expression can be:

Count(distinct CUST_USER_ID)

or (to be sure of the selected date):

Count({<PURCHASE_EVENT_DT={$(=GetFieldSelections(myFieldDate))}>} distinct CUST_USER_ID)

where myFieldDate is the field in the multibox selected by the user

Let me know

Re: Using min function in target field in set analysis

Tue, 10 Jun 2014 12:44:56 GMT

The following set analysis calculates the number of buyers per selected date and not the number of new buyers per selected date.

Count({<PURCHASE_EVENT_DT={$(=GetFieldSelections(myFieldDate))}>} distinct CUST_USER_ID)

If a buyer had had a purchase before the selected date, he should not be appeared as new buyer in the selected date but he should be appeared as new buyer the date that he had done his first purchase.

So, i want to select a date and see the number of users who did their first purchase this date.

Re: Re: Using min function in target field in set analysis

rogerioqv — Tue, 10 Jun 2014 17:50:56 GMT

Dimitra,

See the attachment.

Re: Re: Using min function in target field in set analysis

rogerioqv — Tue, 10 Jun 2014 17:52:07 GMT

Dimitra:

If you can not open the file, use this expression:

='New Customers: ' &COUNT(DISTINCT IF( vDateRef = aggr(min(PURCHASE_EVENT_DT),CUST_USER_ID), CUST_USER_ID))

Re: Re: Using min function in target field in set analysis

Wed, 11 Jun 2014 06:25:29 GMT

Thank you very much. This is exactly what i want to do.

Have a nice day.