6 Replies Latest reply: Dec 20, 2012 11:24 AM by Anthony DELMEIRE

# problem with a set analysis in a count instruction

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 :

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

use aggr function in your expression from starting point

hope it will helps

• ###### Re: problem with a set analysis in a count instruction

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

Hi,

Can you attach sample file.

Regards,

Jagan.

• ###### Re: problem with a set analysis in a count instruction

Maybe like this:

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

• ###### Re: problem with a set analysis in a count instruction

use this

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

hope it helps

• ###### Re: problem with a set analysis in a count instruction

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