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!

[Rohan]

Hi @SariPari , Can you break down your scenario in a simpler way ie instead of putting the Qlik expression, can you tell us the mathematical formula that is required to be calculated here.

Also, I think there is something missing in explaining this part : % Change Year Delta = Year - % Change Year, How are you subtracting Year  & % change?

Regards,

Rohan.

[SariPari]

It has to take the sales for the given year and apply the #% chnage Rate for each year and compund it.

Ex: 2020 sales is $40 - then 2019 it has to take 40 and apply 5.5% change decrease and for 2018 it has to take 2019 +  2018 % change decrease.For 2021 it will be increase of 1.9 % as it is after the given year and for 2022 it will be 2021+2022 % change increase.

[SariPari]

Hi - I have attached the test qvf...not sure why the Sales value is going higher in 2012 and prior - could you please check and let me know what am I missing ?

[SariPari]

@Rohan  - could you please look into this ?

[SariPari]

@tresesco @Chanty4u  - Could you please check this ? Not sure what am I missing.

[tresesco]

@SariPari , I guess it's simple math. Negative power of a smaller number is always greater. Starting from 2012, your base and power both are decreasing. There is nothing wrong in it. It's just math

[SariPari]

@tresesco - If you see 2022 - it should incraese by 8% from 2021, which shouold be $44.02 vs $46.66 and for 2012 it should decrase by 2.1% from 2013 value, which should be $29.56 vs $33.87.

[SariPari]

@steephen @Digvijay_Singh @swuehl @sunnytse129 - could you please check this ? not sure why the expression is not working. Same expression works if I give 5% for all years but not with diiferent % values.

[SariPari]

@Anil_Babu_Samineni @kaushi2020 @jochem_zw @anat - Could you please check this ?