3 Replies Latest reply on Jul 7, 2017 1:34 PM by Alex Braun

    Creating a calculated field that tracks account activity and flags inactivity > 2 months

    Ashlyn Thompson



      I am working on a table that needs to show when a business account ceases to generate activity for more than two months. Ideally, the table would show: Business account numbers that have not generated activity for more than two months, and the date of last activity. I've been trying to come up with a calculated field to do this, but I'm running into issues.


      For one, my dates are formatted as "20xx-0x-0x 00:00:00.000", and I'm finding it difficult to make a rolling two month field from the current date. Keep in mind also, the "current date" may not be the "present day" (ie, today); the "current date" is whatever the most recently recorded date in the database is.


      In addition, the activity being generated in the accounts is kilowatt usage, and it is broken down per hour (ie, "Hr1 KWusage", "Hr2 KWusage", etc). So I would need to sum all 24 hours of the day, and determine if the result is 0 or some positive/negative amount. The only accounts that I'm interested at this point are the ones that have a sum of 0 for more than two months, so ideally only those account numbers would be shown on the table.


      I've been trying to use an If/Then statement, but having trouble mixing the two datatypes.


      Any help would be greatly appreciated!