Future Dated Activities

slgott

Summary

Show Opportunities with Future Dated Activities tied to them

Content

I posted this to the Reporting and Analytics for Engagement Cloud forum but I don't have great luck there so I'm putting it out here too.

Hello,

Our Sales Leaders require our Seller's to have at least one future dated activity on each opportunity.

Business problem we are trying to solve:

“Show me all of the opportunities that don’t have any future dated activities tied to them”.

 

I created a report and am using this Case When Statement to show all of the future dated activities on an opportunity.

CASE WHEN "Activity"."Due Date"> Current_Date then "Sales - CRM Pipeline"."Pipeline Facts"."# of Opportunities" else 0 end

 

The problem I'm running into is this: If I filter on just the zeroes, that would make it seem like Michael Johnson doesn’t have any future dated activities at all (see attached example). But he does. Any ideas on how I can show only the opportunities that only have zeroes and don’t have any 1’s?

If anybody has a better way to get at this information, I'm open to alternatives. If anybody has ideas about how to fix my current solution, I am open to those as well.

 

Thanks,

Stephanie

Aaron Leggett

Hi Stephanie, 

Have you tried the following: 

1) Change your statement to: 

CASE WHEN MAX("Activity"."Due Date") > CURRENT_DATE THEN 1 ELSE 0 END 

2) On the Advanced tab, use the Group By Box to group the query by the unique identifier for each opportunity (e.g. the column formula for the 'Name' column in your output, or the column formula for the Opportunity Number) Click on Apply SQL after this.

Any Opportunity that has a future-dated activity should now have a '1' in the row, regardless of whether the activity date for that row was in the past or future. Likewise, any opportunity without a future-dated activity will now have a '0' in each row. 

Let me know if this works for you

Thanks

Aaron

slgott

This sounds like a great solution. I tried it but got an error. . See attached.

 

 

slgott

I found a solution from Fernando Paes. It worked! Thank you Fernando.

"

the way I would do it, you don't need that column with the formula.

Create a 1st report with just two columns: Opty Name and Opty Id and a filter based on Activity Due date > today (so it gives you the opportunities with an activity in the future).

Then, use that analysis as a filter for a second analysis that will be your main report. So in that, you will filter by opportunity id NOT IN the input of the first analysis. So you will have all opportunities without activities in the future.

I can't think of a more efficient way to do it. Hope it helps."