I'm trying to calculate monthly returns in dollars with the below input. My desired output is a line graph with the "Output" column. Do you know what formula can use to embed this calculation in Qlik?
Do you want to do this calculation in the load script? If so, you could try something like below, making use of the peek() function to access the row above the one being calculated.
// store starting value as a variable let vStartVal = 100;
// load sample data VALUES_0: LOAD * inline [ Month, Input, Jan, 0.50 Feb, 0.44 Mar, 0.72 Apr, 2.00 May, 0.62 Jun, 1.92 Jul, 1.34 Aug, 2.36 Sept, -0.17 ];
// calculate Output using peek function. For the first row, peek() returns null, so use vStartVal instead of peek() VALUES: LOAD Month, Input, if(isnull(peek(Output)), $(vStartVal), peek(Output))*(1+Input/100) as Output
Do you want to do this calculation in the load script? If so, you could try something like below, making use of the peek() function to access the row above the one being calculated.
// store starting value as a variable let vStartVal = 100;
// load sample data VALUES_0: LOAD * inline [ Month, Input, Jan, 0.50 Feb, 0.44 Mar, 0.72 Apr, 2.00 May, 0.62 Jun, 1.92 Jul, 1.34 Aug, 2.36 Sept, -0.17 ];
// calculate Output using peek function. For the first row, peek() returns null, so use vStartVal instead of peek() VALUES: LOAD Month, Input, if(isnull(peek(Output)), $(vStartVal), peek(Output))*(1+Input/100) as Output
Your script works very well! I have a more complicated question.
How could I re-edit the formula if I have the below table with individual monthly returns for all the constituents of a portfolio? I'm using the crosstable formula in the script first to switch the table so it can read the months as one column for a dimension.
The Example of the script is below the table:
Product Name
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sept
Product 1
-0.06%
-0.12%
-0.29%
1.06%
0.47%
0.87%
0.52%
0.34%
0.23%
Product 2
0.40%
-0.55%
-0.03%
1.11%
0.49%
0.32%
0.32%
0.32%
0.17%
Product 3
1.94%
0.76%
0.67%
0.64%
0.95%
0.59%
-0.09%
0.77%
0.71%
Product 4
0.00%
0.00%
0.00%
-1.75%
0.62%
5.74%
5.63%
6.20%
2.81%
Product 5
-0.46%
2.38%
5.37%
5.75%
0.40%
1.06%
1.19%
0.69%
-0.81%
Product 6
-0.90%
1.57%
1.79%
4.10%
0.48%
1.76%
0.50%
2.41%
-2.45%
Product 7
2.15%
-1.54%
4.41%
0.20%
-0.26%
4.53%
2.63%
5.30%
0.10%
Product 8
-1.02%
0.04%
-2.14%
3.20%
0.18%
3.33%
-0.02%
3.15%
-0.92%
Product 9
5.12%
3.07%
-3.09%
2.44%
-0.91%
-1.87%
-5.72%
1.71%
-2.54%
Product 10
-0.30%
-2.70%
-0.20%
4.20%
3.40%
0.70%
3.60%
0.00%
0.00%
Load Editor
[Monthly Returns]:
CrossTable(Month, Returns%, 2) LOAD [ISIN], [Name] AS [Monthly Returns.Name], [Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sept], [L], [M] FROM [lib://AttachedFiles/Returns_Sep.xlsx] (ooxml, embedded labels, table is [Monthly Returns]);
The weights of each product are already loaded from another tab for each product as "[Market Value (%)]".
If I use your script it doesn't seem to work:
let vStartVal = 100;
VALUES: LOAD [Month], [Returns%], if(isnull(peek(Output)),$(vStartVal),peek(Output))*(1+[Returns%]) as Output
Resident [Monthly Returns]; drop table [Monthly Returns];
Do you know how can I modify it so it gives me the same output?
