Problem: I need to pull a report on closed opportunities per week

[rafecint]

Hi all,

We are using Sugar professional Version 5.2.0j

I want to pull a report and find out how many of the open opportunities have been closed during, say, the last 7 days. This is not as easy as I first thought.

At first I filtered the report on [Opportunities > Date Created = last 7 days] and [Opportunities > Sales Stage = Closed won], however that produced a report that showed only the closed and won opportunities, where the opportunity itself has been created during the last 7 days.

Since many of our opportunities are created several months in advance before they are finally closed, the report described above produced inaccurate numbers

So, I changed the filter to [Opportunities > Date Modified = last 7 days] and [Opportunities > Sales Stage = Closed won] thinking that this way the report will display all the opportunities that have been modified into "won and closed" sales stage during the last 7 days. However, it seems that this modification shows all opportunities which have been modified in whatever way the last 7 days (so if someone closed the opportunity a month ago, but corrected the opportunity amount 2 days ago, this will now popp up in my report). This also gives an inaccurate picture of the opportunities that have been closed the last 7 days.

My question is: How do I go about to create a report that will show me a correct amount/number of closed opportunities for the last 7 days? Is there any way I can base a report on the modification of a single drop down field, instead of the modification of an entire opportunity?

Very much obliged for any answers that can point me in the right way.

Best regards,
Rafe

[sugarcane]

Hello Rafe,

Using Pro, you have a few out of the box options.

1. Do you and your users update the 'estimated close date' field in the opportunities module to reflect the actual close date when the opportunity closes? If so, then you can just create a report based on this field and the sales stage.

2. If you do not update the estimated close date field when the opportunity actually closes, then you could always create a customized 'actual close date' field. You can then have your users populate this field when the opportunity closes. You can then create a report based on this new field and the sales stage field.

3. if you do not trust your users to always populate this new field when an opportunity becomes closed won, then you can always create a workflow item that will automatically populate the 'actual close date field' to the date that the sales stage becomes closed won

[rafecint]

sugarcane,

Thank you very much! The third option worked like a charm.

I forgot to mention that the close date in our way of working is defined as the date when we expect to send the invoice (as I don't wan the sales teams thinking that the work is all done just because the solution has been sold). Hence that took out the first option.

Just as you mentioned, I do not trust the salesteams to correctly enter the closed dates in a new field called "closing date" (considering I had to result to some good old fashioned chinese motivation techniques to get them to update the status of the opportunity) , or to resist the urge to tamper with that date.

So I took your advice and created a "closed date" field that does not even show up in the opportunity module, but that is populated by a work flow trigger. Moving forward I can pull my reports based on that value.

Very helpful and very clever! Thanks once again and have a great weekend!