Hello Everyone,
Looking for help.
PFA the screenshot from Excel and Qlik about Requirement and Required / Reality:-
Need to calculate Forecasting on the basis of below formula
ie. ((Last 3 months Avg) + (Merit% * Last 3 Month Avg))
When Jan 2020 is selected, Actuals for Jan 2020 will appear and Forecasting for Feb to Dec 2020 should get calculated.
Similarly, When Feb 2020 is selected; Actuals must be in Jan 2020,Feb 2020 and Forecasting from March to Dec.
Formula Used:
Num(
(
(Sum({<CalendarYear=,CalendarMonthAndYear=, CalendarMonthName=,CalendarMonthNumber=,
CalendarDate = {">=$(=Max((Addmonths(CalendarDate,-3)))) <=$(=Max (Addmonths(CalendarDate,-1)))"}>}[ACT YTD at BUD])/3)
+
(Sum({<CalendarYear=,CalendarMonthAndYear=, CalendarMonthName=,CalendarMonthNumber=,
CalendarDate = {">=$(=Max((Addmonths(CalendarDate,-3)))) <=$(=Max (Addmonths(CalendarDate,-1)))"}>}Merit%*[ACT YTD at BUD])/3)
)
,'$#,##0.0')
Merit% is a field coming from Excel.
