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