Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
pradnya_amol
Creator
Creator

Forecasting in Qlik (Sense) Pivot

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.

 

 

 

 

Labels (2)
0 Replies