Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
mikecherry
Contributor III
Contributor III

Create a Running total using a variable and predefined Columns

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

mikecherry_0-1695380654190.png

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 

Labels (4)
0 Replies