5 Replies Latest reply on Dec 1, 2016 11:41 PM by Norbert Maijoor

    Help needed on calculation!!

    Leeanne Soo

      Hi,

       

      Each Account Code has a mix of Status (Activity/No Activity). Activity definition is based on Type=Call, Email,Demo. Any other Type or Null values are considered "No Activity".

       

      For each Account Code, I need to take the latest date of Activity and mark it as "Active", regardless if there were other "No Activity" associated with it in the past. This is to show that the account is currently "Active". The only time an account would show "Not Active" is if there's only No Activity or Null.

       

      I created a formula called Status v2:

      IF (([Status]="Activity") and ([Status]="No Activity")) THEN "Active"

      ELSE "Not Active"

      END

       

      Example below shows the account code and the latest Active period. Attached is the workbook and csv file for reference.

      Firstly, the Status v2 is showing "Not Active" - which isn't correct.

      Secondly, I need the active period to appear in the latest quarter and not both. This is to show when was the last time this account was active.

       

      Can someone help with this? Thank you!!