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,
