7 Replies Latest reply: Nov 27, 2017 6:27 AM by Aksel Etingu RSS

    Setting up a marker based on historic data

    Aksel Etingu

      Dear all,

       

      I have 3 year worth of data from (31 Jan 2014 until 31 October 2017) for a list of clients with profits.  I would like to have a marker/flag that would mark the client as 1 if the profit has ever been greater or equal to 1 prior to the date being viewed.

       

      ie if I look at customer A in January 2014 the profit is 5, so expected result is 0. But if I take the same customer, A, in March, I want the flag to say 1 as in February it was equal/or greater than the threshold of 10.  I am sure this could be done within a script, but due to being in a corporate environment this tool is restricted.  Is it possible to get an expression/set analysis solution for this?

       

      As always your help is most appreciated!

         

      DatesCustomerProfitExpected result
      31-Jan-14A50
      31-Jan-14B101
      28-Feb-14A101
      28-Feb-14B51
      28-Feb-14C201
      31-Mar-14A81
      31-Mar-14B101
      31-Mar-14C11