HI alll ,
i need to calculate the logic for win rate based on below specification is there any suggestion is much apriciated.
Win Rate
Indicates for new deals the percentage of won deals by dividing number of won deals by the number of won + open deals. The calculation is done for three situations: Snapshot, Rolling 1, Rolling 2.
The calculation requires the following actions to take place:
- For each month identify the new deals (indicated by deal version equal to 1)
- Determine the last known status for a deal within the given time boundary:
- Snapshot: within the same month as the deal was started
- Rolling 1: snapshot + the first following whole month
- Rolling 2: rolling 1 + the first following whole month
- If the status is equal to Won or Open then the deal is included in the win rate calculation. All other cases are ignored.
Note: currently the win rate is only calculated for EMEA region based upon the 1st quote report.
Examples – Regular Situation
Deal Num | Deal Version | First Quote Date | Status |
1 | 2 | 1-JAN-2014 | Won |
2 | 1 | 1-JAN-2014 | Open |
2 | 1 | 1-JAN-2014 | Won |
3 | 1 | 1-JAN-2014 | Open |
3 | 2 | 1-FEB-2014 | Won |
4 | 1 | 1-JAN-2014 | Open |
4 | 2 | 1-FEB-2014 | Open |
4 | 3 | 1-MRT-2014 | Won |
5 | 1 | 1-FEB-2014 | Open |
5 | 2 | 1-FEB-2014 | Won |
Win Rate for January 2013:
- Snapshot = 33%, number of Won Deals = 1, number of Won + Open Deals = 3
- Deal 1 is excluded as it is not the first version in the month
- Deal 2 is included and the last status in the month is Won
- Deal 3 and 4 are included and the last status in the month is Open
- Deal 5 is not started in January 2013
- Rolling 1 = 66%, number of Won Deals = 2, number of Won+Open Deals = 3
- Deal 1 is excluded
- Deal 2 and 3 are included and the last status is Won
- Deal 4 is included and the last status is Open
- Deal 5 is excluded
- Rolling 2 = 100%, number of Won Deals = 3, number of Won+Open Deals =3
- Deal 1 is excluded
- Deal 2, 3 and 4 are included and the last status is Won
- Deal 5 is excluded
below is the i tried with if condition but it's not giving correct results,
=if( [Snapshot Month Status] ='won' and [Deal Version] = 1, SUM ( {< [Snapshot Month Status] = {'Won'} >} [# Snapshot Month Record] )
/
SUM ( {< [Snapshot Month Status] = {'Won', 'Open'}>} [# Snapshot Month Record] ),if([Snapshot Month Status] = 'won' or [Snapshot Month Status] = 'open' and [Deal Version] <>1,SUM ( {< [Snapshot Month Status] = {'Won'} >} [# Snapshot Month Record] )
/
SUM ( {< [Snapshot Month Status] = {'Won', 'Open'}>} [# Snapshot Month Record] )))
Thanks in Advance,
Gireesh
