Hi,
I need to make a forecast for current months, by searching for closes matches in already availible actual data to historical data.
Let me explain:
Rows are days before today. Columns are months. Values represent data for each category of sales in each corresponding month.
1. What I need to do is to take the latest available data for lets say April. That is 92.6% from 74 days ago.
2. Then I want to estimate future values by selecting a value on 195 days row, of the column that has the nearest match by periods. That is closes match to 13 Days April + Closest Match for 44 days April ... etc.
3. For column with the most closest subsequent matches select value at 195 days.
At first I was thinking about it being a subsequent match (e.g. 1st row is closest + 2nd row is closest & so on) and select the whole column which has most row by row closest matches. However, I believe that it is very unlikely that a single column will have subsequent matches, so my next idea is to "score" columns by nearest row match & select the one that has the most of them.
I am familiar with how to fetch the needed variables, what I need help is with coming up with the formula to score the columns and select the correct one. Or maybe I am overcomplicating things and there is a better way to make a prediction/projection. Might it be easier to just create an averaged dynamic(or steps for cumulataive ttl) that I would later apply to the month?
