6 Replies Latest reply: Dec 20, 2012 11:24 AM by anthony delmeire RSS

    problem with a set analysis in a count instruction

    anthony delmeire

      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!