Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

andyquirin
New Contributor III

Caclulating Sales Close Rate with Set Analysis

Hey Qlik-Gurus,

I'm in desparate need of some Set Analysis help.

I’m trying to calculate our Closed Rate % of our opportunities each month and the definition is as follows:

Numerator – Count of Opportunities Closed Won this month with any created date  (the easy part)

Denominator – Count of Opps Created this month with any closed date (what I need help with)

The tricky part is there being two time dimensions (Opp Closed Month and Opp Created Month).

Here’s the current expression calculation:

=COUNT(DISTINCT if(Opp_Stage = 'Closed Won' ,OppNumber))

/COUNT(DISTINCT if(Opp_IsClosed_Flag = 'true' ,OppNumber))

And my dimension is the Opportunity Closed Year-Month – where I need to figure out how to incorporate the Opp Created Year Month dimension in the denominator. Putting a different a different set within an if statement adds another layer of complexity.

Any help or advice here would be super appreciated!

Thanks,

Andy

10 Replies
MVP
MVP

Re: Caclulating Sales Close Rate with Set Analysis

Andy,

I assume one issue is that your records are grouped by your Opp Closed Month, and you then miss records for current dimension Month = Opp Created Month, where Created Month <> Closed Month (assuming that closed month and created month are two fields in the same table).

You can work around this by using a common Date / Month field and distinguis between Closed and Created by a type field, transforming your source table from a cross table to a straight table, maybe like:

INPUT:

CROSSTABLE (Type, Date) LOAD * INLINE [

Opp, OppCreated, OppClosed

A, 01.09.2012, 01.09.2012

B, 01.09.2012, 01.10.2012

C, 01.08.2012, 01.10.2012

];

Then you can use your Date field as dimension and change your expression to

=count({<Type = {'OppClosed'}>} Opp) / count({<Type = {'OppCreated'}>} Opp)

{you can also include your other fields like Stage and IsClosed into these set expressions}

Hope this helps,

Stefan

andyquirin
New Contributor III

Re: Caclulating Sales Close Rate with Set Analysis

Stefan,

Thanks for the help - I think we're in the right direction. We've created a date island as the new common date based on your advice. I tried an iteration of the cross table but couldn't get it to work.

Here's the updated code, but still not working:

Numerator - Count(Distinct{$<YearMonth={"Opportunity_Closed_Year_Month"},Opportunity_ClosedWone = {"true"}>} Opportunity_Number)

Denominator - COUNT(DISTINCT {$< YearMonth={"Opportunity_Created_Year_Month"},Opportunity_Closed = {"true"}>} Opportunity_Number)

Andy

MVP
MVP

Re: Caclulating Sales Close Rate with Set Analysis

Andy,

could you post the relevant script code, too? Or maybe a small sample app?

andyquirin
New Contributor III

Re: Caclulating Sales Close Rate with Set Analysis

Sure thing - thanks for your help!

Opp Table:

SELECT

       YEAR(a.[CREATEDDATE])    as [Opportunity_Created_Year]

      ,MONTH(a.[CREATEDDATE])    as [Opportunity_Created_Month]

      ,YEAR(a.[CLOSEDATE])        as [Opportunity_Closed_Year]

      ,MONTH(a.[CLOSEDATE])        as [Opportunity_Closed_Month]

      ,CAST(YEAR(a.[CREATEDDATE])    as varchar)    + '-' +  CAST(MONTH(a.[CREATEDDATE]) as varchar)    as [Opportunity_Created_Year_Month]

      ,CAST(YEAR(a.[CLOSEDATE])        as varchar)    + '-' +  CAST(MONTH(a.[CLOSEDATE])     as varchar)    as [Opportunity_Closed_Year_Month]

      ,CAST(YEAR(a.[SYSTEMMODSTAMP])        as varchar)    + '-' +  CAST(MONTH(a.[SYSTEMMODSTAMP])     as varchar)    as [Opportunity_Mod_Year_Month]

     

      ,a.[SYSTEMMODSTAMP]        as [Opportunity_Mod_Date]

      ,a.[CREATEDDATE]            as [Opportunity_Create_Date]

      ,a.[CLOSEDATE]            as [Opportunity_Closed_Date]     

            ,a.[NAMEX]                as [Opportunity_Name]

      ,a.[DESCRIPTION]            as [Opportunity_Description]

      ,a.[STAGENAME]            as [Opportunity_Stage]

      ,a.[ISCLOSED]                as [Opportunity_IsClosed_Flag]

      ,a.[ISWON]                as [Opportunity_IsWon_Flag]

     

      ,a.[OPPORTUNITY_ID]        as [Opportunity_Number]

     

  FROM [OPPORTUNITY] a

 

  WHERE

          a.[DELETE_FLAG] = 'N'

          and a.[CREATEDDATE] > '2012-01-01'

------------------------------

Date Island:

LET varMinDate = NUM('1/1/2012');

LET varMaxDate = NUM(today(0));

TempCalendar:

LOAD

    $(varMinDate)+Iterno()-1 AS Num,

    Date($(varMinDate)+Iterno()-1) AS TempDate

AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1 <= $(varMaxDate);

Calendar:

load

     TempDate AS RecordDate,

     date(monthstart(TempDate), 'YYYY-M') AS YearMonth

MonthEnd(TempDate)))) as LastDayOfMonth

    

resident TempCalendar  order by TempDate Asc;

Drop table TempCalendar;

--------------------------------

Dimension:

=If(Opportunity_Closed_Year_Month = YearMonth

or Opportunity_Created_Year_Month = YearMonth

,YearMonth)

Expression:

=Count(Distinct{$<YearMonth={"Opportunity_Closed_Year_Month"},Opportunity_IsWon_Flag = {"true"}>} Opportunity_Number)

/ COUNT(DISTINCT {$< YearMonth={"Opportunity_Created_Year_Month"},Opportunity_IsClosed_Flag = {"true"}>} Opportunity_Number)

------------------------------

Goal:

calculate our Closed Rate % of our opportunities each month and the definition is as follows:

Numerator – Count of Opportunities Closed Won this month with any created date

Denominator – Count of Opps Created this month with any closed date

Re: Caclulating Sales Close Rate with Set Analysis

HI

Try like this

Count(Distinct{$<YearMonth={"$(=Max(Opportunity_Closed_Year_Month))"},Opportunity_ClosedWone = {"true"}>} Opportunity_Number)


MVP
MVP

Re: Caclulating Sales Close Rate with Set Analysis

Andy,

that's not the same approach I suggested, my approach does not create a data island. One can probably work out a solution using a data island, but I think this is not needed here (and should be avoided, since it adds complexity and is not as performant, IMHO).

Please check attached (mostly the same as posted above, tried to adapt a little closer to your table layout).

Regards,

Stefan

andyquirin
New Contributor III

Re: Caclulating Sales Close Rate with Set Analysis

Hey Stefan,

Thanks for sending over the example.

On your Input table, how would I populate this portion without typing it in manually?

01.09.2012, 01.09.2012, 10.10.2012, A, Aa, ??, true, true, 1

01.09.2012, 01.10.2012, 10.10.2012, B, Bb, ??, false, false, 2

01.08.2012, 01.10.2012, 10.10.2012, C, Cc, ??, true, false, 3

01.09.2012, 01.09.2012, 10.10.2012, D, Dd, ??, false, false, 4

01.10.2012, 01.10.2012, 10.10.2012, E, Ee, ??, true, true, 5

01.08.2012, 01.10.2012, 10.10.2012, F, Ff, ??, true, false, 6

I've got around a million opportunities I need automatically inlcuded.

Thanks for your help - I really appreciate it.

Andy

MVP
MVP

Re: Caclulating Sales Close Rate with Set Analysis

That's an INLINE LOAD:

LOAD *

INLINE [

CREATEDDATE,CLOSEDATE,SYSTEMMODSTAMP,NAMEX,DESCRIPTION,STAGENAME,ISCLOSED,ISWON,OPPORTUNITY_ID

01.09.2012, 01.09.2012, 10.10.2012, A, Aa, ??, true, true, 1

01.09.2012, 01.10.2012, 10.10.2012, B, Bb, ??, false, false, 2

01.08.2012, 01.10.2012, 10.10.2012, C, Cc, ??, true, false, 3

01.09.2012, 01.09.2012, 10.10.2012, D, Dd, ??, false, false, 4

01.10.2012, 01.10.2012, 10.10.2012, E, Ee, ??, true, true, 5

01.08.2012, 01.10.2012, 10.10.2012, F, Ff, ??, true, false, 6

];

I tried to use your source table field names, so to replace the INLINE Load with your data source, just use

SELECT *

FROM [OPPORTUNITY];

If needed, adapt the field names in my sample code.

andyquirin
New Contributor III

Re: Caclulating Sales Close Rate with Set Analysis

Hey Stefan,

I loaded my opportunities table and the Year_Month field are all populating null.

Any ideas?

Thanks,

Andy

OppYearMonth.jpg

Community Browser