topic Re: Caclulating Sales Close Rate with Set Analysis in QlikView

Caclulating Sales Close Rate with Set Analysis

andyquirin — Wed, 26 Sep 2012 19:55:04 GMT

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

Re: Caclulating Sales Close Rate with Set Analysis

swuehl — Thu, 27 Sep 2012 09:31:40 GMT

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

Re: Caclulating Sales Close Rate with Set Analysis

andyquirin — Thu, 27 Sep 2012 21:58:54 GMT

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

Re: Caclulating Sales Close Rate with Set Analysis

swuehl — Thu, 27 Sep 2012 22:06:43 GMT

Andy,

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

Re: Caclulating Sales Close Rate with Set Analysis

andyquirin — Thu, 27 Sep 2012 22:26:12 GMT

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

MayilVahanan — Thu, 27 Sep 2012 22:44:36 GMT

Try like this

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

Re: Caclulating Sales Close Rate with Set Analysis

swuehl — Fri, 28 Sep 2012 15:29:00 GMT

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

Re: Caclulating Sales Close Rate with Set Analysis

andyquirin — Fri, 28 Sep 2012 15:37:51 GMT

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

Re: Caclulating Sales Close Rate with Set Analysis

swuehl — Fri, 28 Sep 2012 15:48:51 GMT

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.

Re: Caclulating Sales Close Rate with Set Analysis

andyquirin — Fri, 28 Sep 2012 16:23:30 GMT

Hey Stefan,

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

Any ideas?

Thanks,

Andy

Re: Caclulating Sales Close Rate with Set Analysis

swuehl — Fri, 28 Sep 2012 16:29:22 GMT

Please compare your script to the one I've attached above.

I don't create Opportunity_Closed_Year_Month / Opportunity_Created_Year_Month at all. These fields are replaced by Opp_Type and Year_Month in the CROSSTABLE Load.