# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Action-Packed Learning Awaits! QlikWorld 2023. April 17 - 20 in Las Vegas: REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor III

## need help for below condition

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] )))