3 Replies Latest reply: Jul 5, 2018 12:35 PM by Shahbaz Khan Mohammed 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.