Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
use aggr function in your expression from starting point
hope it will helps
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.
Hi,
Can you attach sample file.
Regards,
Jagan.
Maybe like this:
mid(concat(Status,','),index(concat(Status,','),'X',-1)+2,1)
use this
mid(Status,index(status,',',1)+1,1)
hope it helps
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