need help for below condition

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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 1^st 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

topic need help for below condition in QlikView

need help for below condition

Win Rate

Examples – Regular Situation