Oracle Transactional Business Intelligence

Future Dated Activities
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
Comments
-
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