Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
adelmeire
Contributor II
Contributor II

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 : 

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!

6 Replies
er_mohit
Master II
Master II

use aggr function in your expression from starting point

hope it will helps

adelmeire
Contributor II
Contributor II
Author

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:

Microsoft Excel - Classeur1.jpg

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

jagan
Luminary Alumni
Luminary Alumni

Hi,

Can you attach sample file.

Regards,

Jagan.

Gysbert_Wassenaar

Maybe like this:

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


talk is cheap, supply exceeds demand
er_mohit
Master II
Master II

use this

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

hope it helps

adelmeire
Contributor II
Contributor II
Author

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