2 Replies Latest reply: Nov 27, 2017 6:30 AM by Antonio Gouveia RSS

    Lead to Opportunity conversion

    Antonio Gouveia

      Hi,

       

      I'm trying to get the lead to opportunity conversion rate from some data and for that I need to count the number of opportunities from a certain year and divide it by the count of leads from the same year and I would need to be able to have it doing for all across all years that my data provides. These two tables are connect by the OwnerID.

       

      I've tried to use the following expression but I can't divide those two values with a count function, I guess:

       

      count({ < OwnerId= {"=([Opportunity.CreatedDate.autoCalendar.Year] = [Lead.CreatedDate.autoCalendar.Year])"}>}[Opportunity.Id])/[Lead.Id]

       

      Any ideas?

       

      Best regards,

      Antonio

        • Re: Lead to Opportunity conversion
          Settu Periyasamy

          Maybe try like this..

           

          Put the closed paranthesis in the end..

           

          count({ < OwnerId= {"=([Opportunity.CreatedDate.autoCalendar.Year] = [Lead.CreatedDate.autoCalendar.Year])"}>}[Opportunity.Id] /[Lead.Id] )


          or


          count({ < OwnerId= {"=([Opportunity.CreatedDate.autoCalendar.Year] = [Lead.CreatedDate.autoCalendar.Year])"}>}[Opportunity.Id] )

          /

          count({ < OwnerId= {"=([Opportunity.CreatedDate.autoCalendar.Year] = [Lead.CreatedDate.autoCalendar.Year])"}>}[Lead.Id] )


            • Re: Lead to Opportunity conversion
              Antonio Gouveia

              Hi Settu,

               

              Still doesn't work...

              I'm actually working with QlikSense and I'm trying to have a KPI object to display the percentage of leads that were converted into opportunities. However, I have two different fields with dates: one for the lead created date and the other for the opportunity created date. In the sheet I'm trying to have it being displayed I have a filter for the opportunity created year and whenever I filter it by year I will get a value that it is not representative because the leads field is using the opportunity created year to return a value. For some reason, the lead count is not the same when I use the opportunity created year or the lead created year fields.

               

              Not sure if there's a way to show the conversion rate in a KPI object when a filter the years by the opportunity created year and have it counting the number of opportunities and the number of leads, but using the lead created year field for the latest?

               

              Best,

              Antonio