How to do a Recursive Calculation in Qlik Sense to create a Forecast or Projection

[mattdavis10]

I would like to do a recursive calculation where the above number is used to calculated the new number in a column. In  the attached excel sheet I use the above above balance  + the above balance * a percentage of change to calculate the new balance. I would like to create a expression that uses actual data when present, but otherwise does a recursive calculation using above number to continue the calculation until the periods end. 

 

[dwforest]

have you tried Above function?

https://help.qlik.com/en-US/sense/February2020/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/Inter...

[mattdavis10]

The above function doesn't allow for recursive calculations.  I've tried and  it only does the calculation when actual data is present. I want to be able to continue the calculation even when actual data isn't present.  

[sunny_talwar]

Try this expression

Sum(TOTAL_RF93_BALANCE)
+
If(Flag = 'F',
FirstSortedValue({<Flag = {"A"}>}TOTAL Aggr(Sum(TOTAL_RF93_BALANCE), PAY_PRD_END_DATE), -PAY_PRD_END_DATE)
*
exp(RangeSum(Above(Log(Only({<[Flag] = {'F'}>} 1+[RF93 Change])), 0, RowNo()))), 0)

Where I have created a Flag for forecasted data in the script

[mattdavis10]

Thank you Sunny for the help! This is what I need, but for some reason I get an error when trying to use this expression. Just says Error in Expression. 

[sunny_talwar]

I have attached the QVF for you to check as well.. can you check if the expression works in the attached sample when you open it?

[mattdavis10]

Our company uses a web based Qlik Sense so I do not have software to open a QVF file. Any suggestions?

[mattdavis10]

Do  I need to create a flag column to use this expression? This is still very new to me. 

[sunny_talwar]

You can import the qvf file into your web based Qlik Sense platform.

[sunny_talwar]

All we need is a way to identify what is actual and what is forecast periods. If there is already a way to identify this information, you can use that instead of the flag field that I created.