I'm working on an Inventory Health and Monitoring system that takes sales history, current stock and pending orders into account. I'm using sales history to determine the projected sales and have realised that I need to change the way the data is loaded. Stock is current and sales data is provided as TM, TM-1, TM-2, TM-3 etc... I have used a cross table to bring the sales data in as month but I need to transform the TM, TM-1, TM-2, TM-3 to actual dates and I'm struggling.
Do I change the load script to load the sales history as actual dates (TM-1 as [Load Date]-30), (TM-2 as [Load Date]-60) etc.. ?
Sorry if my explanation is incomplete, can anyone provide some guidance?
