Oracle Transactional Business Intelligence
Future Dated Activities
Show me only the Opportunities that don't have a future dated activity added
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.