1

I have a question about the stagehistory. I am trying to get the number of times an opportunity enters a specific phase during, for example, this week (or lastw eek). I tried this query:

select count(StageName) from OpportunityHistory where StageName = 'x' and CreatedDate = THIS_WEEK

But the problem here is that whenever you change the CloseDate, Probability or Amount it also counts as an entry, and the opportunities will count double (count distinct will not work either because there could be a change in amount of an opportunity this week that was put in the stage last week).

I tried to use this query:

SELECT count(id), newvalue, oldvalue from OpportunityFieldHistory where Field = 'StageName' and NewValue = 'x' and OldValue != 'x'

But Salesforce somehow doesn't allow me to put NewValue and Oldvalue in the where part of the query (I am also not sure whether this would get the result I want).

Is there anyway I can get the result I want?

Thank you, Martijn

Improve this question

1 Answer 1

Reset to default
-1

Try this:

SELECT Stagename, SUM(amount) FROM Opportunity GROUP BY Stagename
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.