Hi, I have this problem that has been kicking my *** for the last few days, and I need your help for a hot minute.
Context: I'm developing a model who's function is to reveal gaps in inventory i.e. gap analysis. The products are car parts, and whether or not we have a part in stock for a particular vehicle model.
Let's say the bosses wants to figure out what are the gaps among all vehicle models between the model-years 2010 and 2000, and only for vehicles with over 1,000 registrations. Under these conditions I will find that we have 17 products we do not cover and this is correct.
Problem: However, the issue is that in the event a car model has the same number of registrations, it will always show the first one on the list regardless if it breaks the conditions or not. What I do not understand is why the formula ignores the production year and stock conditions.
Some suggestions I've found on the net requires sorting or manual work in each case. This is not viable as the real data has 70,000 different vehicles to consider. Moreover, the whole point of this project is automation and removing the need for manual sorting.
I've included some real data and the formulas I've used to illustrate the problem, and hope some of you can give me some insight whether or not this is a solvable problem.
Kind regards,
Pål
Bookmarks