4 Replies Latest reply on Nov 25, 2014 7:14 AM by Jerry Pudmanski

    counts using 2 dates

    Jerry Pudmanski

      Hi All,

       

      Attached find a workbook I am currently working on.

       

      The first tab, labeled "leads", is pretty straight forward.  It shows the number of leads by month generated by our locations.  The second tab, "registrations" shows the count of registrations that have come from the leads.    I am showing the count of the registrations by month from the date of the lead.  I did this to show the life span of a lead.

       

      I tried combining the two in the "test" tab but the data is off.   It spreads the leads over the months which I don't need.

       

      I would like to combine this data from the lead and registration tabs. Since these are 2 different dates who can I accomplish this? Do a need another "month" calculations.

       

       

      Thanks

        • 1. Re: counts using 2 dates
          KK Molugu

          Jerry:

          I would like to combine this data from the lead and registration tabs. Since these are 2 different dates who can I accomplish this? Do a need another "month" calculations.

           

          What do you mean by 'would like to combine this data'. Can you mock something up to show what you want to view. Thanks.

           

          ..kk

          • 2. Re: counts using 2 dates
            Jerry Pudmanski

            If you look at the first tabs called leads and the second tab called registrations.  I am looking to combined the two.  Problem is the first count is based on the inquiry date while the second one is a formula counting out the months out from the lead to registration.

             

            basically I have two different tables. I would like to show the month of the lead and the month the lead comes a registration.  Ideally I would like to have it look like the "registration" tab.

             

            It would look like something attached for the year 2013. First column is the leads by month and the second column is the registrations from the months of the lead.

            • 3. Re: counts using 2 dates
              KK Molugu

              Jerry:

               

              I see what you are trying to accomplish with both dates. I don't know if there is an easy way other than creating a duplicate source and joining on the months (month of inq date) to month (number of months between inq and reg dates).

               

              Having said that, I have created couple alternative solutions and hope this works for you;

               

              Option 1: If all you are trying to in total leads by month and how many have become registrations, wouldn't the following be a clean representation of the data;

              NOTE: Grand total is the total leads

              option-1.PNG

               

              Option 2: Create two different sheets and show them on the dashboard. This will look like what you are trying to accomplish, but as a dashboard.

              option-2.PNG

              Again, your requirement might be different, but seeing month as the number and having different meanings for what you are showing seems a little confusing. What I mean by (in your xls mockup or Option 2), you are treating 1, 2, 3, etc. in column 1 as Jan, Feb, Mar, etc for leads and number of months for registration from lead for the other. My 2 cents.

               

              Hope this helps

              ..kk

              • 4. Re: counts using 2 dates
                Jerry Pudmanski

                thanks for you help. I think either method may work and I tried attempting the side by side already. I was hoping to combined them but I don't think there is an easy way.