Create a Running total using a variable and predefined Columns

[mikecherry]

Hi Guys,

Hope you are well. 

I want to re-create the same functionalities I have in an excel spreadsheet into a Qlik Dashboard.

I have attached the spreadsheet with the formulas.

The idea is:

for a set of given Cost and the first FMV  I want to calculate all the Estimated FMVs and Distributions based on two predefined variables, one that impacts the FMV - FMV Growth and the other the Distributions - Dist Pace. (they are set at the top left of the spreadsheet)

The formula for Distributions starts from the second month and uses also a predefined variable

The Formula for the Estimated FMV is: =MAX(0,G22+H14-H15+G22*((1+$C$8)^(1/4)-1))     (Picture 1)

Table  (see below) is the data I have loaded in: 

I want to be able to basically fill in the gaps in the FMV column and the distributions based on the two variables

I have tried to do this in the load script with this:

Let vGrowth=$(vGrowthFrontEnd);

Load*,

    Rangesum((Peek(Estimated_FMV,-1)+Cost-Dist)+Peek(Estimated_FMV,-1)*(Pow((1+$(vGrowth)),0.25)-1),FMV) as Estimated_FMV,

    (Pow((1+$(vGrowth)),0.25)-1) as Growth;

CashFlow_1:

LOAD

    rowno(),   

    "Date",

    Cost,

    Dist,

    FMV,

    Dist-Cost as Net_Cashflow, 

  Rangesum(Peek(Cumulative_FMV_New,-1)+Cost-Dist,FMV) as Cumulative_FMV_New

FROM [lib://QlikShare/01_DataInput/02_ManualFiles/Private Wealth/FMV Modelling/Generic Deal Profile.xlsx]

(ooxml, embedded labels, table is Sheet2);

But something is not working as I wanted.

Has anybody solved for something like this? Should I use Loop For? 

Picture 1

Table

Date	Cost	Dist	FMV		Dist Pace	Growth Rate
31/12/2022	80	0	87.91209		30%	12.50%
31/03/2023	2.5	0	0
30/06/2023	2.5	0	0
30/09/2023	2	0	0
31/12/2023	2	0	0
31/03/2024	1.5	0	0
30/06/2024	1.5	0	0
30/09/2024	1	0	0
31/12/2024	1	0	0
31/03/2025	1	0	0
30/06/2025	0.5	0	0
30/09/2025	0.5	0	0
31/12/2025	0.5	0	0
31/03/2026	0.5	0	0
30/06/2026	0.5	0	0
30/09/2026	0.5	0	0
31/12/2026	0.5	0	0
31/03/2027	0.5	0	0
30/06/2027	0.5	0	0
30/09/2027	0.5	0	0
31/12/2027	0	0	0
31/03/2028	0	0	0
30/06/2028	0	0	0
30/09/2028	0	0	0
31/12/2028	0	0	0
31/03/2029	0	0	0
30/06/2029	0	0	0
30/09/2029	0	0	0
31/12/2029	0	0	0
31/03/2030	0	0	0
30/06/2030	0	0	0
30/09/2030	0	0	0
31/12/2030	0	0	0
31/03/2031	0	0	0
30/06/2031	0	0	0
30/09/2031	0	0	0
31/12/2031	0	0	0
31/03/2032	0	0	0
30/06/2032	0	0	0
30/09/2032	0	0	0
31/12/2032	0	0	0
31/03/2033	0	0	0
30/06/2033	0	0	0
30/09/2033	0	0	0
31/12/2033	0	0	0
31/03/2034	0	0	0
30/06/2034	0	0	0
30/09/2034	0	0	0
31/12/2034	0	0	0
31/03/2035	0	0	0
30/06/2035	0	0	0
30/09/2035	0	0	0
31/12/2035	0	0	0
31/03/2036	0	0	0
30/06/2036	0	0	0
30/09/2036	0	0	0
31/12/2036	0	0	0
31/03/2037	0	0	0
30/06/2037	0	0	0
30/09/2037	0	0	0

@sunny_talwar