Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a table of sales by supplier:
Month | Supplier | Sales |
Jan | A | 1488 |
Jan | B | 1301 |
Jan | C | 1267 |
Feb | A | 1373 |
Feb | B | 1307 |
Feb | C | 1228 |
Mar | A | 1816 |
Mar | B | 1101 |
Mar | C | 1885 |
And a table of sales by customer:
Month | Customer | Sales |
Jan | X | 2668 |
Jan | Y | 978 |
Jan | Z | 410 |
Feb | X | 815 |
Feb | Y | 348 |
Feb | Z | 2745 |
Mar | X | 1515 |
Mar | Y | 1734 |
Mar | Z | 1553 |
I want to create a table that shows sales by customer AND supplier that would look like this:
Month | Supplier | Customer | Sales |
Jan | A | X | 979 |
Jan | A | Y | 359 |
Jan | A | Z | 150 |
Jan | B | X | 856 |
Jan | B | Y | 314 |
Jan | B | Z | 132 |
Jan | C | X | 833 |
Jan | C | Y | 306 |
Jan | C | Z | 128 |
Feb | A | X | 286 |
Feb | A | Y | 122 |
Feb | A | Z | 964 |
Feb | B | X | 273 |
Feb | B | Y | 116 |
Feb | B | Z | 918 |
Feb | C | X | 256 |
Feb | C | Y | 109 |
Feb | C | Z | 863 |
Mar | A | X | 573 |
Mar | A | Y | 656 |
Mar | A | Z | 587 |
Mar | B | X | 347 |
Mar | B | Y | 398 |
Mar | B | Z | 356 |
Mar | C | X | 595 |
Mar | C | Y | 681 |
Mar | C | Z | 610 |
I have no other information to link the 2 tables other than date as it's just forecast information. How is this possible?
Thanks
I have a question - both tables have sales but one is for customer and the other is for supplier - If you wnated to put everytung in one table, wouldnt you need sales for both?
if you wnat to create one table from 2 and you want columns from both, you would need to do a join
Its just forecast information so I have sales by customer or sales by supplier but I don't have both. That's what I want to create in the script. A join doesn't work as I get null values for supplier where there is a customer and vice versa
I still do not understand which of the 2 Sales fields you have, you are showing in your consolidated table
attached is the output based on the sample data you provided (I created 2 excel spreadsheets and renamed the sales fileds to avoid synthetic key
are you looking for a relationship between customer and supplier?
can you attach the qvw so I can have a look at what you did?
sure - see attached
based on my data in January supplier A supplied 36.7% of all sales in January therefore supplier A sold 36.7% of sales to customer X. 36.7% of 2668 is 979. Does that make sense?
I think without knowing which supplier supplies to which customer and the value of sales between them, it wouldn't be possible to create the right model.
I just want to assume that proportion of sales by supplier applies to each individual customer. I know that in January supplier A supplied 36.7% of all sales in January therefore supplier A sold 36.7% of sales to customer X.
I agree based on the numbers in January(total customer sales of 4056) and supplier sales for Supplier A (1488) equates to supplier A accounting for 36.7% of the January. I am not sure that the percentage is evenly split amongst the customers, unless that is a assumption or understanding you have