Set Analysis pulling data from 2 different tables problem

[Anonymous]

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.

[andrespa]

Same issue here, not sure if it's possible to use set analysis using fields that are in different tables (even if those tables are linked). If that's the case, then one option could be to introduce a mapping table and then recreate the field needed on the fact table.

Any ideas on this someone?

[ogautier62]

Hi,

you don't have Year in table 2 ?

unique cost for a product ?

because it should work :

sum({< set analysis>} quantity ) * cost

[MK9885]

Without Year or Date in 2nd table it won't be easy to get the 2017 Cost of Part #.

Since there is no date associated to your cost of part in 2nd table not sure how to get the result except generate/map with dates