0 Replies Latest reply: Aug 24, 2017 11:50 AM by harlow hunt RSS

    Set Analysis pulling data from 2 different tables problem

    harlow hunt

      I am having difficulty pulling information from two different sources.

       

      I am trying to use a Set Analysis to pull in the total cost of all sales from a particular location from 2017.

       

      I am able to calculate the # of sales in 2017 by using the following expression:

       

      (Count({$<[Location_Year]={'A Retail2017'}>} [Product-Product #])+Sum({$<[Location_Year]={'B Retail2017'}>} [Product-Product #]))

       

      My issue is getting the cost associated with each of the sales.

       

      This is because the cost associated with each product is in a different table.

       

      Table 1 - This table has transaction data.  It tells me the following:

       

      • Order #
      • Part #(s) sold
      • Sale Amount of each part # sold
      • Date of the sale
      • Year of the sale

       

      Table 2 - This has basic product information:

       

      • Part #
      • Retail Price of Part #
      • Cost of Part #

       

      So I'm trying to determine the total costs associated with each part # sold (# of sales for the part * cost of the part) for 2017.

       

      Any help would be greatly appreciated.