Create a Running total using a variable and predefined Columns

mikecherry — Fri, 22 Sep 2023 15:54:43 GMT

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

topic Create a Running total using a variable and predefined Columns in App Development

Create a Running total using a variable and predefined Columns