7 Replies Latest reply on Jan 8, 2014 12:51 PM by Matt Lutton

    Getting latest state in a time series

    Omar Serang

      I know this should be simple but I'm stuck trying to aggregate that latest state value per user in a set of user time series data. Here are the individual rows:

       

      userdatestate
      lisa1/1/2013unknown
      lisa1/2/2013signup
      lisa1/9/2013signup
      lisa1/10/2013activated
      lisa1/11/2013activated
      lisa1/12/2013active
      lisa1/13/2013idle
      bart1/1/2013unknown
      bart1/2/2013signup
      bart1/9/2013signup
      bart1/10/2013signup
      bart1/11/2013activated
      bart1/12/2013activated
      bart1/13/2013activated

       

      I want to be able to rollup to just the user's names and see the lastest state:

       

      userstate
      lisaidle
      bartactivated

       

      Example worksheet attached with this data.