Calculate interest in a variable period

pacoli2013 — Tue, 10 Aug 2021 14:40:33 GMT

Hello,

I need some help. I have a document with sales and payments. Between to dates I have calculated the days and the cumulated balance (Sales minus payment)

For cumulated balance I have used the previous and peek function in the script:

if(KUNNR = previous(KUNNR), peek(SaldoWRBTR) - Sum_SaldoWRBTR, Sum_SaldoWRBTR) as SaldoWRBTR, /* BalanceAmount WRBTR in EURO */

The same I did for calculated days:

if(Previous(KUNNR)= KUNNR, Peek(NumBUDAT) - NumBUDAT, num(date(Today())) - NumBUDAT) as CalculatedDays

For the interest calculation I use an expression in my script :

= if(sum(SaldoWRBTR)> 0, sum(SaldoWRBTR) * (CalculatedDays/365) * ($(vPercent)/100), 0)

Interest is only calculated when my cumulated balance is greater null

Now I need to calculated the Interest amount in a month or in a specific period (f.e. from 05/02/2019 till 05/05/2019

Because the number of sales and payments is every time different, I cannot use simple filters like month.

I have tried the expressions:

sum({<Date ={">=$(=vStartDate) <=$(=vEndDate)"},Month=,Year=>} SaldoWRBTR)

sum({<Date ={">=$(=vStartDate) <=$(=vEndDate)"},Month=,Year=>} (SaldoWRBTR * (CalculatedDays/365) * ($(vPercent)/100) )

I have added an example in a excel sheet

But those don’t work. I hope someone can help me, thanks in regards Court

topic Calculate interest in a variable period in App Development

Calculate interest in a variable period