I am looking for the duration for each OppStage an Opportunity has been in.
I have looked in Historical Trending, Opportunity History and Opportunity Field History - none of these carries the correct dates being:
- Valid From
- Valid To (Am I correct, that this carries the previous change (any change) to the Opp
If I use "Stage_isUpdated" = 1 and, for each row, calculate Stage Duration by ValidTo - ValidFrom = X days , it does not produce the correct duration.
The Sales Analytics App does not produce the correct dataset for this either via the Dataflow.
I can easily create a recipe calculating this, but is there not an OOTB solution?
Solution:
The Pipeline Trending carries this info.
The StageIs_Updated field, cannot be used to calculate stage duration if filtered on.
What you need to do is to include all the rows from the historical datasat and in, e.g. a Compare Table, group on stages, then to divide the sum of Duration with the unique amount of OppIds (not OppHisIds) - this will leave you with an avg. dur for each stage.
