Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
scottokeefe14
Partner - Contributor
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:

 

scottokeefe14_0-1624980129094.png

 

 

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

 

scottokeefe14_1-1624980244630.png

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
scottokeefe14
Partner - Contributor
Partner - Contributor
Author

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

edwin
Master II
Master II

scottokeefe14
Partner - Contributor
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.

 

 

scottokeefe14
Partner - Contributor
Partner - Contributor
Author

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

chrismarlow
Specialist II
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;

20210630_2.png

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;

20210630_1.png

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

Cheers,

Chris.