Cumulative with Multiplier

[scottokeefe14]

Hello,

I am making a retirement calculator of sorts where a user inputs their age, rate, expected growth, etc. I am having trouble getting the rate of return to multiple against the previous years balance. I believe the correct answer would look like this in excel:

However, when trying in qlik sense I get the following:

the first column includes the 8% annual return, and shows that the initial contribution matches. The Second doesnt include it, but does not match after the initial contribution obviously. Here are the respective formulas. I am using variable sliders to let the user control:

rangesum(above(if((ValueLoop($(Age),$(RetAge),1) - $(Age))=0,$(CurrentBalance),

($(CurrentBalance) +

($(Salary)*
(pow(
(1+$(SalaryIncrease)),(ValueLoop($(Age),$(RetAge),1) - $(Age))
))*
$(Contribution)
)
)
*
(1+$(AnnualReturn))

),0,rowno()))

I've also tried using the above function without rangesum but it takes the calculated value of the above cell and not the cumulative to include its above call.

I feel like there is a simple answer I'm overlooking and appreciate any help you can give.

Thanks!

[scottokeefe14]

I guess just to add, anything that would give me (previous cell * the rate of return) + current period contribution 

[edwin]

try  rangesum(above(yourexpressionHere),0,3)  not rowno()

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

[scottokeefe14]

that did not work. Seems to have just made it add the first 3 rows and nothing after.

Basically I just need the (cell total above * (1 + return)) + Annual contribution defined as:

if((ValueLoop($(Age),$(RetAge),1) - $(Age))=0,$(CurrentBalance),

($(CurrentBalance) +

($(Salary)*
(pow(
(1+$(SalaryIncrease)),(ValueLoop($(Age),$(RetAge),1) - $(Age))
))*
$(Contribution)
)
))

The contribution part works just fine.

[scottokeefe14]

added what I was working on as a QVF if anyone is willing to take a stab.

[chrismarlow]

Hi,

I also can't see a way of applying the growth to the prior periods correctly, either through accumulation or above/rangesum.

I have looked trying a UDF, that you pass row number raise and return, however I don't seem to be able to use either looping or recursion, therefore this is a bit of a brute force attempt.

I've done for 5 years, you would expand the range of years by adding in extra rows of formulae inbetween row 31/32, they are going to get long and messy (could potentially use Excel to write out the formulae & copy paste in), but I think this is possible & your range of years should have a natural limt (?), but I don't know if Qlik will break before you get to that;

I've also not looked at your current value and age sliders or how they are incorporated (or in fact used sliders at all), but I think that should be simpler, if this bit works, which (for my 5 year sample), looks like it might;

Attached the QVF, do post back if this works or if you find a more elegant solution.

Cheers,

Chris.