how to create in SAP C4C a report that measures the days an opportunity has been in a sales phase

[dcerruti]

Good morning,
I need to create a report that measures in days how much the opportunities have been in a specific sales phase.
If you go to the different opportunities you can see the history (the dates of when they entered the phase and the end date with the relative duration).
I would like to point out that I have already configured the opportunity history in the scoping.
I tried to use the Opportunity History as a data source but it does not retrieve that data.
The problem is that when I try to create the report no data source retrieves that data. It is not possible to create Key Figures because the data available based on the different data sources do not have the necessary data (e.g. end date of phase - start date of phase).
Furthermore, I have verified that using the opportunity hader as a data source it is possible to analyze the days in which the opportunities are in a sales phase, but when they exceed it they are no longer counted.
Any suggestions?

[Anna_Bondarenko · Accepted solution]

I recommend using the data source "Opportunity History" and calculating the time difference in all phases by using the Validity From Date and Validity To Date fields. Please note that fields with the same name are available both in the Key Figures list and in the Characteristics list, so make sure you are selecting the correct ones when setting up your calculations.

Keep in mind that the end date for the last phase is calculated from the date of its assignment until 30.12.9999 (#). Therefore, it is better to calculate the difference up to the current date, and if it is the last phase, use this difference in days instead of Validity To Date - Validity From Date.

Hence, the calculation process can be as follows:

1. Create a calculated key figure "Period days" as Validity To Date - Validity From Date.
2. Create a calculated key figure "Period to current" as Current Date - Validity From Date.
3. Create a calculated key figure "Period If Last Status" based on Period to current, if Validity To Date is greater than tomorrow.
4. Create a calculated key figure "Period If Not Last Status" based on Period days, if Validity To Date is less than tomorrow.
5. Create a calculated key figure "Total Days in Phase" as Period If Last Status + Period If Not Last Status.

[dcerruti · Accepted solution]

First of all thank you very much. But I am not clear on points 3 and 4. What are the formulas to create them? Thank you very much!

[Anna_Bondarenko · Accepted solution]

3) (ValidityToDate GT Tomorrow) * Period to current and aggregated total on SalesPhase, 4) (ValidityToDate LT Tomorrow) * Period days and aggregated total on SalesPhase