2 Replies Latest reply on May 19, 2016 3:12 AM by Jakub Tutaj

    How to count Max Activity Date of an account/user when there are activity/inactivity periods

    Jakub Tutaj

      Hi,

      any suggestions on how to find max activity date for each account in following data set?

       

      download?id=uLOgBbDJECFrrBZumddsr9R7zH5pgH

       

      Here's what I tried:

      I've created 2 calculated fields:

       

      #1 Active Account - for a given month it will return account_id or NULL

      If (active > 0) THEN account_id ELSE NULL END

       

      #2 Max Activity Date - this should populate all rows with MAX Activity Date

      {FIXED [Active Account]: MAX([date])}

       

      What I expect is:

       

      account_iddateactiveActive AccountMax Activity Date
      11/1/2016111/1/2016
      12/1/20160NULL1/1/2016
      21/1/20160NULL2/1/2016
      22/1/2016122/1/2016

       

      What I get is skewed Max Activity Date (account_id = 1 has wrong Max Activity Date)

       

       

      account_iddateactiveActive AccountMax Activity Date
      11/1/2016112/1/2016
      12/1/20160NULL2/1/2016
      21/1/20160NULL2/1/2016
      22/1/2016122/1/2016

       

       

      I guess it's because of the order that FIXED works. It first looks for all Active Accounts and finds two of them (account_id=1 and account_id=2). Then it looks for max date not caring about "Active Account" field anymore.

       

      How can I get MAX date for when active = 1?