# How to make a historical index of a group of materials in which the set of materials changes every month?

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.

-
This is more relevant for a Operation Research or Supply Chain Management StackExchange. – Alexey Kalmykov Jul 10 '13 at 9:15
To answer your question: don't remove data. You should be more precise on your goal. What do you want to see ? – Were_cat Jul 10 '13 at 10:07
If it is about data visualisation you should try on Cross validated. – Were_cat Jul 10 '13 at 10:08
Agree with Alexey and Imorin. Would also add that this sounds like an inventory question also, which implies that there are accounting rules on it. – John Jul 10 '13 at 18:59