4 Replies Latest reply on Sep 24, 2018 4:46 PM by Shinichiro Murakami

    Calculation Countd when looking up date per LeadID

    Renske Hercules

      Hi all,

       

      Hope you can help with the following. I am currently creating a dashboard based on data coming out of 22 countries. In the end I only want to filter on Country + Specific time range, and then it will show the following:

       

      - #Leads

      - #Appointments

      - #Sales

      - Lead to Sale conversion >This will be an easy Sale/Leads calculation, but the above 3 metrics should be easy to use in a calculation.

       

      Below is a sample of my raw data (I also attached dummy data). The calculation should work as following:

      - Leads > Sometimes the status shows "new" But this is not the case for all Lead IDs. Therefore it should look at the lead ID number > look at the lowest date > if this is in the daterange selected in my main filter > then it should be Lead ID as countd.

      - Sales & Appointments > Countd(lead ID's) when status is "appointment made" or  "Sold"

      - Lead to Sale conversion >This will be an easy Sale/Leads calculation

       

      I tried a lot with CASE,COUNTD, and other functions, but for some reason I just can figure out how to make it work.

       

      Hope someone can help!