Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am perplexed.
I have 2 data tables:
The 2 tables are joined by these 3 fields
I need to calculate the return rates in Parts per Million PPM = (Returned/Shipped)*1000000.
Where I run into issues is calculating PPM for a given Customer or Part by POF and/or FM
What want to get is
Customer | POF | Volume | Retunred | PPM |
---|---|---|---|---|
Customer 1 | FIeld | 1,000,000 | 1 | 1 |
Customer 1 | Production | 1,000,000 | 2 | 2 |
Customer 1 | 0km | 1,000,000 | 0 |
What I get is:
Customer | POF | Volume | Retunred | PPM |
---|---|---|---|---|
Customer 1 | FIeld | 1,000,000 | 1 | 1 |
Customer 1 | Production | 1,000,000 | 2 | 2 |
Customer 1 | 0km | 0 |
I can not come up with a correct Set Analysis where by it will give be back the total volume for the customer unless there is a value for the POF. This is a simplified version and the acutal data, where I get differing values for Volume (which should reman constant) depending on the number of returns......
Thank you fo any thoughts anyone might have..........
I won't be able to help you with doing this in set analysis but typically in a Data warehouse with a many to many table query issue, you need a driving table between the two tables to ease the join to get the results you expect. I would suggest structuring the data to satisfy the reporting requirement in the load. that would also simplify the set analysis.
my .02$
Good luck!