3

I am trying to create a report which would let me identify the sales reps who have not had any revenue in last 90 days. I am applying date filter of "Last 90 Days", and then a filter for revenue = 0, but this filters all the reps who might not have had revenue even in one of the 3 months where as I want revenue to be 0 in all 3 months. Can some one please help me with this? Thanks in advance.

Improve this question
1
  • If you use "last quarter" that might get you a contiguous period instead of separate months. Problem is you wouldn't get the rolling 90 days, but something along those lines might be worth a try.
    – crmprogdev
    Commented Apr 1, 2014 at 19:33

1 Answer 1

Reset to default
2

Define "revenue". Are you talking about Opportunity owners? Or maybe you're using team selling and opportunity splits? Forecasts? Some completely custom object (seems like you have something unique per rep per month)?

Here's a rough idea, you'll have to adjust it to fit your scenario.

  1. Create a report on Users (not Opportunities with Owner or something, just Users).
  2. In the Filter: Add menu select cross filter.

enter image description here

If you're more familiar with SOQL syntax it would be something more or less like that:

SELECT Id
FROM User
WHERE Id NOT IN (SELECT OwnerId 
    FROM Opportunity 
    WHERE Amount > 0 AND CreatedDate = LAST_N_DAYS:90)
Improve this answer
2
  • Thanks for the response. Yes that is correct. What I have in my custom object is that there is 1 (unique) entry for every sales rep for every month. There is a Sales Revenue field for each record called "Sales_Rev__c". and there is a "Report_Date__c" field which refers to 1st of that month (i.e. 3/1/2014). Would the solution that you have suggested work here? Also I could not follow the part where you are applying condition for Users without Opportunities.. Can you please explain that part too? Thanks a lot for your help.
    – Robin
    Commented Apr 1, 2014 at 20:38
  • It should work :) Not sure how I can describe it in more detail... The first "line" in my screenshot says "Filters: Add" - click the dropdown arrow, select Cross Filter... youtube.com/watch?v=_OVBhPpPM7M (check also standard help & training). It will work like a charm as long as your object has a real lookup relation to User (and not say a text field)
    – eyescream
    Commented Apr 1, 2014 at 21:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .