Reporting on special data

  • 1
  • Idea
  • Updated 5 months ago
  • Implemented
I have a customer that is wanting to build a report on which accounts have NOT been updated in the last 7 days. 

Every way we have tried to run the report will not return accurate data. (L2 Confirmed). 

In other words: I want to build a report that will list all of the accounts that have not been update in the last 7 days. 

Currently this is not an option, I think it would be beneficial for many based on this scenario alone. 
Photo of Cameron Wright

Cameron Wright, Employee

  • 1,232 Points 1k badge 2x thumb

Posted 5 months ago

  • 1
Photo of Sai Ram Pulluri

Sai Ram Pulluri, Official Rep

  • 7,090 Points 5k badge 2x thumb
Hi Cameron, Sorry for the inconvenience,could you check whether below option works for you? 
If the account is updated the last modified date will be changed to updated date,so could you try to build report on last modified date.

Please let me know if I am missing anything 
Photo of Cameron Wright

Cameron Wright, Employee

  • 1,232 Points 1k badge 2x thumb
We tried this method and found that the dates that were left have been updated in the last 7.
Photo of Shagun Tewari

Shagun Tewari

  • 82 Points 75 badge 2x thumb
Hi Sai Ram, 
We tried filtering on both activity date and last modified date, it looks like it just returns all the records that were updated/modified before last 7 days regardless if there was any recent activity on these accounts.
Our goal is to track all the accounts where timeline has not been updated in the past 7 days. 

Photo of Karl Rumelhart

Karl Rumelhart, Official Rep

  • 8,310 Points 5k badge 2x thumb
Hello,  I suspect that the confusion here is that the question asked about the Account itself being updated vs Timeline being updated.  To get customers who have not posted on Timeline in the last seven days you can build a report on the Activity Timeline object as below.  [Max of Activity Date by (Account) Name.  -- watch out, there are a lot of 'Name' fields -- filtered by Max Activity Data < Last 7 Days.  I also filtered to active customers] . 

What is going on her is that the Activity Timeline table has one record for each Activity.  You find the most recent one for each Company and see if it is more than seven days ago. 

A few subtleties
  • If you are using Relationships this will pick up a post on ANY Relationship.  If you want to look at things at the Relationship level you need to build the report "By" Relationship Name or ID
  • This is looking at when the latest post was saved.  It is possible a post was modified after this date.  You could look at Last Modified date if you prefer. (This date is on a per-Activity basis.)
  • THIS IS THE TRICKIEST ONE --  If a customer has NEVER had a Timeline activity then it won't get picked up here.  There are no records in the table for that Customer at all so it won't be in this table at all.  
If you want to deal with this 'no Activities at all' problem you will need to use the Rules Engine.  There are two approaches.  First, you can add a field to the Company table in MDA (or you could use the Account table in Salesforce) for "most recent Activity Post."  You can build a rule that populates that column with data from the Activity Timeline table.  Accounts for which there has never been a post will show up with a Null in that column.  Alternatively, you can use a Bionic Rule to perform an outer join of the Activity Timeline table with the Company table.  [The Outer Join will keep the records from the Company table that don't have any match in the Activity Timeline table -- that is, there has never been an Activity posted for that Customer.] . So you can pick out the ones that never had any posts. 

So the short answer is that if you aren't worried about 'absence of data' then it will be pretty easy to build this report.  To address Customers with zero posts will be a bit more involved. 

I hope this helps.