Future Dated Activities

slgott

Summary

Show me only the Opportunities that don't have a future dated activity added

Content

Hello,

I'm struggling to think through how to do this. Here is the business problem we are trying to solve: We want our sales people to always have at least one activity tied to the opportunity that has a date in the future. They can have many activities on an opportunity with dates in the past, dates in the future, or no date at all. I would like to show my sales leaders the opportunities that don't have a future activity at all or is blank. Here is what I've tried so far: 

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

When I do this and the sales leaders filter on just the '0', they may be looking at opportunities that actually do have future dated activities on them (because remember, you can have many activities to one opportunity). 

I've also tried creating an analysis that shows me all of the opportunities that do have a future dated activity and then using that as a filter on another analysis where the opportunity ID does not equal one on that list. That doesn't seem to be working either because the seller may have opportunities with past or current date that won't show on that list because they also have a future date. Does that make sense? 

Basically, if you can't tell, I might just need help thinking through the best solution to this. I've spent hours on this and I fear the answer is very simple and right in front of my face but I can't seem to grasp it. Any help would be greatly appreciated. 

Thank you, 

Stephanie

MaartenDIF

Hi Stephanie,

You could use the RANK function to create a helper column and filter on this.

I used this in a bit different situation, but I think it might work for you as well. We needed a deduped export of our employees, but the multiple (emergency) contacts added by employees were showing up one row each. We added a filter with formula RANK("Contact Phones"."Phone Number" by "Workforce Management - Worker Assignment Real Time"."Worker"."Employee Email Address") = 1 (OR null, to show people without contact).

Your case might work by creating a column with
RANK("Activity"."Due Date" by -some unique column on opportunity level-)

and then applying a report filter which selects (don't forget the brackets):
"Activity"."Due Date" > Current_date AND (RANK-column = 1 OR RANK-column is null)

Example	RANK(chronological_key, null, year_key_columns)
Description	Calculates the rank for each value satisfying the numeric expression argument. The highest number is assigned a rank of 1, and each successive rank is assigned the next consecutive integer (2, 3, 4,...). If certain values are equal, they are assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7...).

Hope this helps,
Maarten