Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Challenge in calculation based on 2 sets of data

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.

1.png

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:

2.png


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,

0 Replies