topic Re: Accrual Percent (Investment Income) Calculation - Excel equivalent to Product() or Rollup values in App Development

Accrual Percent (Investment Income) Calculation - Excel equivalent to Product() or Rollup values

EddieG — Tue, 23 Jan 2024 23:15:18 GMT

Good evening,

I'm hoping for some help with a calculation in the load script. I typically find most answers here on the forums or figure it out on my own, but this one is stumping me. I might just be missing something simple.

I need to do what I'm being told is an investment income calculation. Part of the calculation involves multiplying the current month's percent times the previous month's accrued percent:

Excel Column/Row	A	B	C
1	Month	Percent	Accrual Percent
2	1/31/2023	100.4024%	103.0595%
3	2/28/2023	100.4287%	102.6465%
4	3/31/2023	100.4073%	102.2083%
5	4/30/2023	100.4188%	101.7938%
6	5/31/2023	100.4528%	101.3693%
7	6/30/2023	100.4541%	100.9124%
8	7/31/2023	100.4563%	100.4563%

June is 100.4541% * 100.4563% OR PRODUCT(B7:B8)

May is 100.4528% * 100.9124% OR PRODUCT(B6:B8)

...

Jan is 100.4024% * 102.6465% or PRODUCT(B2:B8)

I need to calculate the Accrual Percent and I'd like to do it in the load script. I'm thinking I could do some loops and get it done that way, but that seems too messy. Hoping for a more elegant solution.

With that said, I just realized that I could just do the Accrual Percent in excel (it's just one value per month, changing each month) and load that into Qlik for the rest of the calculations... but now I'm wondering if this could be done in-house.

Thank you for your time.

Re: Accrual Percent (Investment Income) Calculation - Excel equivalent to Product() or Rollup values

F_B — Mon, 09 Sep 2024 10:10:07 GMT

Hi @EddieG ,

You can calculate the cumulative product using a FOR loop within the load script.

First load data sorted by month to correctly calculate the cumulative product, then:

DataWithAccrual:
LOAD
Month,
Percent,
1 AS AccrualPercent // Initialize with 1
RESIDENT Data //Your data source table
ORDER BY Month;

LET vAccrualPercent = 1;

FOR i = 1 TO NoOfRows('DataWithAccrual')
LET vPercent = Peek('Percent', i - 1, 'DataWithAccrual');
LET vAccrualPercent = $(vAccrualPercent) * $(vPercent);
UPDATE DataWithAccrual SET AccrualPercent = $(vAccrualPercent) WHERE RecNo() = $(i);
NEXT i;

Hope this help

Re: Accrual Percent (Investment Income) Calculation - Excel equivalent to Product() or Rollup values

Kushal_Chawda — Mon, 09 Sep 2024 11:00:17 GMT

@EddieG Assuming your percentage values are stored in numbers format if not you need to convert it to number format

Data:
Load Month,
Percent
FROM Table;

New:
Load *,
num(if(RowNo()=1,Percent, Peek(AccrualPercent)*Percent),'#0.0000%') as AccrualPercent
Resident Data
Order by Month desc;

Drop Table Data;