The question may sound simple however for the moment it is a brainteaser to get it right, let me explain:
the exercise is to be done on +/- 200 groups of materials (matgroups)
one matgroup can contain 5 to 5000 materials. We want to see a price or cost evolution by month going back 3 years.
Every month the materials we buy have a price and a purchased quantity. However not all materials are bought every month.
so matgroup X can consist of materials A, B, C, D with each a price and quantity in january, but in february the same matgroup X can consist of materials A, D, E, F, G, H. It can take months until material B or C is purchased again. The weights (=quantities) of the materials can be very different within the matgroup.
our 1st solution is to only keep materials in the index that have been bought in every month. Out of a set of 20.000 materials, we in the end had 3000 materials left that were valid... some matgroups only had 1 material left, some 0. No surprise that this would give very strange trends, and not very representative.
2nd solution was to fill the blanks if a material isn't bought, however if this is a material with quite some volume next to it, it gets too important to be an artificially added material...
We're a bit stuck. Any experiences with something similar? Do you have enough info to think about an answer? Any suggestions/comment welcome.