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
