topic Re: problem with a set analysis in a count instruction in QlikView

problem with a set analysis in a count instruction

adelmeire — Thu, 20 Dec 2012 11:35:24 GMT

Hi,

I need some help about a formula.

I have a table with 3 columns, which gives me the customer's status for each month:

- customer

- period

- status

I would like to count the number of customers who have an Y status the month after having an X status for the last time, inside the periods selected.

I tried something like that to have the number of customers per status for the month after having the last status X :

count({$<period={'$(=addmonths(max({<status={X}>} period), 1))'}>} distinct customer)

but the set returns the max period of all customers not for each customer.

Do you have any idea to solve my problem?

Thanks for your time and support!

Re: problem with a set analysis in a count instruction

er_mohit — Thu, 20 Dec 2012 12:15:30 GMT

use aggr function in your expression from starting point

hope it will helps

Re: problem with a set analysis in a count instruction

adelmeire — Thu, 20 Dec 2012 12:33:49 GMT

thanks for your help but I tried

aggr( count({$<period={'$(=addmonths(max({<status={X}>} period), 1))'}>} distinct customer), customer)

and

count({$<period={'$(=addmonths(aggr(max({<status={X}>} period), customer), 1))'}>} distinct customer)

with no result.

Here what I would like:

but what my formula renters is the last value of each customer.

Re: problem with a set analysis in a count instruction

jagan — Thu, 20 Dec 2012 12:46:30 GMT

Hi,

Can you attach sample file.

Regards,

Jagan.

Re: problem with a set analysis in a count instruction

Gysbert_Wassenaar — Thu, 20 Dec 2012 12:57:08 GMT

Maybe like this:

mid(concat(Status,','),index(concat(Status,','),'X',-1)+2,1)

Re: problem with a set analysis in a count instruction

er_mohit — Thu, 20 Dec 2012 13:11:57 GMT

use this

mid(Status,index(status,',',1)+1,1)

hope it helps

Re: problem with a set analysis in a count instruction

adelmeire — Thu, 20 Dec 2012 16:24:49 GMT

Thanks for your help!

here a sequence a status: 11, 11, 12, 11, 11, 15

with your help, I found the next value (15) following the last 11 value.

But now, I want to find the previous value (12) before the last 11 value. Do you have another any idea?

Thanks