Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am new to Qlikview and have the following requirement and need some guidance on how to approach it
The user wants to take historical data (Orders for last 6 months), get the total by day of week (Mon, Tues etc) and then get a daily % of the Weekly Total
example:
Historical data | Sun | Mon | Tue | Wed | Thur | Fri | Sat | Weekly total |
---|---|---|---|---|---|---|---|---|
Data by resource for last 6 months | 600 | 500 | 400 | 200 | 50 | 780 | 300 | 2830 |
% of weekly total | 21% | 18% | 14% | 7% | 2% | 28% | 11% | 100% |
The next step in the process will then be to take a weekly forecast number (Note that this is not available by day) and apply the % calculated above to this number to get a weekly forecast number
Eg Forcast for week 2800
Forecast | Sun | Mon | Tue | Wed | Thur | Fri | Sat |
---|---|---|---|---|---|---|---|
Forecast for week (2800) | 594 | 495 | 396 | 198 | 49 | 772 | 297 |
This will then be used to calculate a daily variance.
Thanks in advance for help on this
Soreen
Add a weekday(Date) as DayOfWeek to your table. Then:
Allocation:
LOAD
DayOfWeek
,sum(Something) as DaySomething
RESIDENT SomeTable
WHERE conditions that narrow you down to the 6 months you want
GROUP BY DayOfWeek
;
LEFT JOIN (Allocation)
LOAD sum(DaySomething) as TotalSomething
RESIDENT Allocation
;
LEFT JOIN (Allocation)
LOAD
DayOfWeek
,DaySomething / TotalSometing as DayAllocationPercent
RESIDENT Allocation
;
Now I'm not sure what the forecast data looks like, but it's possible you'll be able to do something like this:
LEFT JOIN (Forecast)
LOAD
DayOfWeek
,DayAllocationPercent
RESIDENT Allocation
;
LEFT JOIN (Forecast)
LOAD
ForecastKeyField
,DayOfWeek
,WeeklyForecastAmount * DayAllocationPercent as DailyForecastAmount
RESIDENT Forecast
;
DROP TABLE Allocation
;
Hi John
Thanks for your quick feedback. Will let you know if this solved the problem.
Soreen