Calculate compound rate

[SariPari]

Hi - I have the issue where I need to calculate the expense Increase /Decrease based on the Rates and the year.

I have the solution using variables but that is not the ideal way of doing this for sure.This method is messing the performace as I have almost 3000 Address with different Construction years.I have the working app attached.

I have data as below and the required calculation is in the screenshot (Column named Calculation)

Ex:

2020 is $40
2019 = 2020's value - % change of 2019 = 40 - (40 * 5.5) = 37.80
2018 = 2019's value - % change of 2018 = 37.80 - (37.80 * 5.6) = 35.70
2017 = 2019's value - % change of 2017 = 35.70 - (35.70 * 5.0) = 33.92
so on until 2010

for year 2021 onwards it will add the % change

2021 = 2020's value + % change of 2021 = 40 - (40 * 1.9) = 40.76

2022 = 2021's value + % change of 2022 = 40.76 - (40.76)= 44.02

so on until 2023.

Any help is much appreciated.

@theoat  @henrikalmen @stevedark @marcus_sommer @sunny_talwar @swuehl @hic @Gysbert_Wassenaar @jagan 

Thank you so much!

[Anil_Babu_Samineni]

@SariPari Like @tresesco mentioned this is simple math and work in the same way for all rows.

I guess the sorting maybe not expecting as it should? Try this.

Sum(Aggr((SUM(Sales) * pow((1+(% Change Year)),% Change Year Delta), ([% Change Year], (Numeric, Ascending))))

[SariPari]

@Anil_Babu_Samineni - Thank you Anil....but that did not work.It gives the same output.I have the QVF attached with an example...could you please check that ?

[Anil_Babu_Samineni]

@SariPari Great, thanks for the QVF. Now, can you please explain the logic what you expect from or In 2012? 

[jochem_zw]

The maths look correct

(Checking if this (!@#(@)!# BOT is still active)

[vinieme12]

numbers look ok until 2013 but in 2012 it is going up instead of going lower...not sure what is wrong.

Can you explain the highlighted bit please? why would you expected it to go lower ?

based on your calculation  = (SUM(Sales) * pow((1+(% Change Year)),% Change Year Delta)

for 2023; you are compounding a rate for 5% for 3 years, it would go up! right ?

[SariPari]

@Anil_Babu_Samineni Here is the required calculation and the output.

Thank you so much.

[SariPari]

for 2023 it should take the value of 2022 and apply 8% growth and for 2023 it should take value from 2022 and apply 5%....where as for 2019 and beyond it should take the values of future year and subtract.

2019 will take 2020 value and apply 5.5% decrease and 2018 will take 2019 value and apply 5.6% decrease and so on...only 2010 value will incrase because the rate is -ve.

[vinieme12]

In that case it should  be 1- not 1+

[SariPari]

Both expressions(1+ and 1-) gives the same output !!

[SariPari]

@Anil_Babu_Samineni @tresesco  - Could you please check if this is doable at all ? Thank you so much!