Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
gireesh007
Contributor III
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] )))

Thanks in Advance,

Gireesh

0 Replies