5 Replies Latest reply on Feb 21, 2015 5:10 AM by KK Molugu

    Instance of Value Relative to Date + Date of Event on Other Row

    james.diaz

      Hi there. Hoping I can get some help with some calculated fields I need to create.

       

      Except for the row column, I have data on automatic extensions given for failed automatic payments as follows:

       

      rowdateuseraction
      1

      1/1/2015

      jack@something.comextension
      21/5/2015george@emailaddress.comextension
      32/8/2015jack@something.comextension
      42/20/2015jack@something.comcurrent
      52/21/2015george@emailaddress.comcurrent
      62/25/2015samantha@whatever.comextension

       

      Background: The first time a user appears (first relative to date), it is because their automatic payment failed and they are given an extension (action) automatically. At the end of an extension, the payment is attempted again automatically to make the user's account current (hence current in action) and avoid service interruption for nonpayment. Multiple extensions are given in a row if the user continues to fail (e.g., jack and george).

       

      Now, how can I create the following calculated fields in such a way that they are not affected by any filters:

       

      • calculated field with the instance # for the user based on date. e.g., jack is on 3 rows, so I would want this field to return a 1 for row 1, a 2 for row 3, and a 3 for row 4, as this identifies the distinct instances of jack in order based on date.
      • calculated field with the count of records for that user (i.e., max of calculated field above)
      • calculated field with the earliest date a user has current in the action field. e.g., all rows where jack is a user (rows 1, 3, 4) would show 2/20/2015, because that is the first time the action is "current" for jack.

       

      Please let me know if anything is unclear. Thanks for your time!