Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andyquirin
Partner - Contributor III
Partner - 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
swuehl
MVP
MVP

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
Partner - Contributor III
Partner - Contributor III
Author

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

swuehl
MVP
MVP

Andy,

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

andyquirin
Partner - Contributor III
Partner - Contributor III
Author

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

MayilVahanan

HI

Try like this

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


Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
swuehl
MVP
MVP

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
Partner - Contributor III
Partner - Contributor III
Author

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

swuehl
MVP
MVP

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
Partner - Contributor III
Partner - Contributor III
Author

Hey Stefan,

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

Any ideas?

Thanks,

Andy

OppYearMonth.jpg