Hello Ada Napierala, 

Can you clarify where are you running this query? 

When trying it to run against a MySQL Database the error that I have is related to the opportunities.date_created field that doesn't exist. 
Replacing it with opportunities.date_entered the query runs fine.

Hi Andre Serrazina,

Yes I just realized that I was using incorrect field name. However, I also realized that the field date_closed is showing me the signature date of the opportunity, which is not the date I want to use. 

I want to use the date from activity stream - the date a user changed the opportunity to closed won. 

Is that possible? 

You are right, the  date_closed stores the Expected Close date that can be set by the user before the Opportunity is closed.
The information that you are looking for is not stored by default in Sugar. If you want to store it at an Opportunity level you'll need to create a field and populate it when the Opportunity is closed. This can be done via SugarBPM, Logic Hook or workflow.   

Retrieving that info from the activity stream will not be easy, the data column in the activities table will store the record name, id and the before and after value of the field that is audited. 

You can have a look at what is stored in the activities table for a given Opportunity querying for that row like this: 

SELECT * FROM activities where parent_id={opportunity_id};    

OK here we are.

1. Create a datetime field on Opportunities

2. Create a SugarBPM set sets the current time once the status changes to Closed Won or Closed Lost


3. Create another field "Opp Age"

and add the following formula which gives you the number of days between both timestamps (date entered and closing date)

abs(subtract(daysUntil($date_entered),daysUntil($closing_date_c)))

4. An opp that is calculated looks like this

5. create a summation report with details with criterias as you like that shows the average age of your opps  :-)

Bests
Björn

Wow, that's cool! Will give that a try too :D Thank you 

for existings opps you can try to set the expected closed date to the new date field first via sugarlogic (calculated value) and then run "Recalculate Values"


With this existing records have their age calculated too :-)

Bests
Björn

for existings opps you can try to set the expected closed date to the new date field first via sugarlogic (calculated value) and then run "Recalculate Values"


With this existing records have their age calculated too :-)

Bests
Björn

Hey Björn Canales Pfisterer

I followed your steps to recreate the Opp Age. I do get the opp Age, but it's incorrect :( 

Also, your closing date seems to be an imprint, not something you add yourself, whereas mine is empty and to be added manually on all opps, including Closed Won. 

Hey, yes I played with test data, and set the closing date with a mass update...

for updaing existing records do the following:

Go to the field settings of the new closing date field and set the follwing calculated formula (I chosed expected closedate as you mentioned above it will work for you)

Tthen go to the opportunities Module list view, mark all opportunities

Then run Recalculate values

Once you are done, remove the formula you have set above.
Now all existing Opps have expected close date = closing date.
If the formula you have set on Opp Age is correct it should also be calculated fine.

For all new Opps SugarBMP should set the date automatically

Bests

Björn

Hey Björn Canales Pfisterer

Yes, the process is working, however it has a flaw unfortunately. Opportunities that are with Signature Date e.g. 30.06.2023 and created in 30.06.2022, but are already closed lost will still show the opp age of 365 days... 

Do you have any solution for that? Is there no way of capturing the Date closed of the opportunity as the date it has been physically closed lost / won by the sales person? 

Thanks 

Ada

So you mean because the workflow only sets the date to "today" when the Opp is won? Create a second SugarBPM that does the same if a Opp get lost.

Or is this for existing Opps that are already lost and you are looking for the correct timestamp? If this is the case I think this might be solved with an SQL statement via database similiar to the one you've asked for initally but in my experience if we implement a new solution in an existing instance especially "lost opps" from the past are not that important and can be filtered out for a while in reports if everyone else using this reports agrees.

If an SQL statement is needed for updating the records keep in mind Sugar Support is able to run it on your instance but you need to deliver the working SQL statement to them.

Hope that helps :-)

Bests

Björn