3 Replies Latest reply on May 27, 2016 7:03 AM by Carolyn Marks

    Combining two different data sources but not linking them (Excel)

    Carolyn Marks

      Or maybe I'm trying to link them and I just can't figure out how.   I'm connecting to an excel workbook.  One tab has new business created.  The other has new business sold.  I want to calculate a close rate by month.  The denominator is easy--new business created by create month.  The numerator is new business sold but using the close date--if it's the same as the create date of the NB created.

       

      So I'm NOT using the same exact opportunities to calculate close rate.  However, I'm matching the close month to the create date.  I have tried this every way.....so forgive me if I didn't create the extract.  I have a tab there for expected output.

        • 1. Re: Combining two different data sources but not linking them (Excel)
          Carl Slifer

          Howdy Carolyn,

           

          In order to solve your problem I used data blending. In fact this is probably the easiest method in this case. Data Blending allows values to be aggregated and then linked together. In this case I will be summing up your amounts and then linking them from the created sheet's create date to the sold sheet's closed date.

           

          First Get things Set Up Right

          1) I first connected to your excel sheet and then only chose the created table. Rename this datasource CreatedData

          2) I connected to another data source and only chose to use the sold table. Rename this data source CreatedData

          3) I selected the data menu from the ribbon and went to edit relationships.

          4) I chose my CreatedData as the primary and SoldData as secondary

          5) I then selected the custom radial selection and removed everything

          6) I added in a relationship from created from created datasource to closed from sold data source.

           

          Now to Build the Table!

          1) I right-click and dragged your created data - from the created data source to the rows shelf

          2) Right-Click Amount in this datasource and rename it 'Created'

          3) Drag that to text

          4) Go to the Sold datasource and rename amount to 'Sold'

          5) Drag that field onto the text inside the data table you've built already.

          6) Return to your primary datasource and Create a calculated field so that the SUM([SoldData.Closed]) / SUM([CreatedData.Created])

          7) Drag that field onto your measure values card

           

          I've attached a workbook to assist you with this.

           

          Cheers!

          Carl Slifer

          InterWorks

          • 2. Re: Combining two different data sources but not linking them (Excel)
            Carolyn Marks

            Thanks, you got me 90% there.  Blending the data is the way to go, just was having trouble figuring how to link them.....

             

            Question if you know.  I have to put in another piece of criteria.  I need to filter on PRODUCT (which I actually created groups for but after playing around it doesn't appear I can use them in formulas?)--but where the group product =Supply in EITHER the primary (created) or secondary source (sold).

             

            Is my only luck building an if or statement on the ungrouped PRODUCT and then putting that in my filter?  How can I do "OR" statements between the two that I grouped (or maybe the answer is that I cannot?)

            • 3. Re: Combining two different data sources but not linking them (Excel)
              Carolyn Marks

              Never mind!  Figured it out.  I need to choose MY (month year) when data blended and not the full date.  I also need to pull BOTH the Products into the filter view.  Phew.  Took me a while.  Thanks for your help.