Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We have Product dimension table, Forecast & Update data.
Frequency of data:
Forecast data comes on monthly basis whereas Update data comes every quarter start basis.
Scenario:
We have the current data model; product table is link with fact table based on SG_CODE.
Fact & Calendar linked using MONTH_YEAR_KEY.
In the front end these Month & Year are displayed. User selects the year & months and data gets refresh in the dashboard.
There are 2 new tables as mentioned above: Forecast & Update data
Please see the attached excels for forecast and update data.
Forecast and Update table is linked to product table based on JCODE & ZCODE respectively.
Based on the JCODE & ZCODE we have to extract SG_CODE from Product table for both Forecast & Update table respectively and then we have to concatenate these two tables’ contents to FACT table along with newly generated MONTH_YEAR_KEY field(as we have month & year fields).
This would look like this:
The above mentioned data model is derived.
Forecast is having a field called FORECAST_AMT and Update is having field called NET_SALES
When the user selects Year & Month on the Front End, we have to display a measure called YTG (Year To Go) along with few dimension.
YTG Calculation:
If user select Year as 2014 & Month as Feb, YTG would be
= (average of FORECAST_AMT from Feb 2013 till Dec 2014 ) / (No. of Months from Feb 2013 till Dec 2014)
Approach:
We need to keep all historical data of Forecast & Update as well, so we have newly added column called AS_ON which will the MONTH-YEAR whenever the data is arrived in both the tables.
If the month selected by user is Quarter start month i.e. Jan/April/July/Oct then the YTG calculation must refer Update table data NET_SALES
Else YTG calculation must refer forecast table data FORECAST_AMT.
Let say current month is March-2014
User has selected Feb-2014, since Feb is not the quarter start month we have to refer Feb-2014 month’s FORECAST_AMT in Forecast table for YTG calculation.(considering AS_ON field)
In case if user would have selected Jan 2014, then we have to refer Jan-2014 month’s NET_SALES field in Update table (since it is start of a quarter) for YTG calculation.
So considering a particular month whether it’s a quarter start or normal month selected by user from front end, we have to calculate YTG from the respective month‘s Update & Forecast data.
Appreciate if someone can guide how to implement this approach.
Thanks in advance,