10 Replies Latest reply: Sep 28, 2012 12:29 PM by Stefan Wühl RSS

    Caclulating Sales Close Rate with Set Analysis

    Andrew Quirin

      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
          Stefan Wühl

          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
              Andrew Quirin

              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
                  Stefan Wühl

                  Andy,

                   

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

                    • Re: Caclulating Sales Close Rate with Set Analysis
                      Andrew Quirin

                      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