# App Development

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for
Did you mean:
Partner - Contributor

## Cumulative with Multiplier

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!

5 Replies
Partner - Contributor
Author

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

Master
Partner - Contributor
Author

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.

Partner - Contributor
Author

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

Specialist II

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.