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

Hello,

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!

Thanks,

Ashlyn

• ###### 1. Re: Creating a calculated field that tracks account activity and flags inactivity > 2 months

Hello Ashlyn,

Your Calculation for the Last activity would be.

It will give you the max date per customer.

[Current Date] = {fixed customer: Max([Activity Date]) }

Then to generate your no activity, use the following.

sum(

if [Activity Date] >= dateadd('month',-2,[Current Date]) then [KWHr]

end)

Basically, this checks if your activity is within the last two months, if it is, then give the Power Usage, else give null.  Then sum the KWHrs together.

• ###### 2. Re: Creating a calculated field that tracks account activity and flags inactivity > 2 months

Thanks, Alex! And would these be two separate calculated fields?

Also, I am very new to Tableau so still trying to figure things out, but the [Current Date] and [Activity Date] are not fields I have in my database, so would I need to create separate calculated fields for them as well?

Thank you.

• ###### 3. Re: Creating a calculated field that tracks account activity and flags inactivity > 2 months

The [activity date] is whatever date/time field you are using for each transaction, and the [current date] is calculated from the [activity date].  I prefer to have some separate calculation for more complex formulas, it makes it easier to diagnose issues or hone in on a desired result for that specific piece, for instance.

we have our [Activity Date] and we want to find the last date possible, I would begin by creating a new calculated field. I might try doing max([Activity Date]) which would give me the last date of anyone's activity, but we want the last date for a specific customer, so I can do a fixed calc on customer to get the last date per customer.

Separation of the pieces also allows them to be reused easily.