RangeSum in loading script

AJZ — Wed, 05 Jul 2023 08:42:35 GMT

Hello!

I have an issue when trying to calculate the cumulative value over a period of time and can't find an answer in the community.

I want to sum up the value for each month and need it to group by MonthStartDate, company name, account code and costing code.

For example, if MonthStartDate = 2016-12-01, Company A, Account = 1, Costing Code = X have amount 100.

For the next month, 2017-01-01 the amount is 150 for the same company, etc., I would like to have the value 250.

As of now it returns 100 and then 150.

When using

(Sum(Aggr( Rangesum(Above(Sum(SUM_AmountEUR),0,RowNo())), (ReferenceDate1, (Numeric, Ascending)))))

in a table it works perfectly, but I need it in the script.

What I do is I create zeroes for the months not containing any data, as I need it for every month, and then I sum the amount. Thereafter, I use the RangeSum-function and order by MonthStartDate, company name, account code and costing code

In the rangesum-function I have also tried do add the sum-function and group by once more but it does not work.

What am I missing?

Here is the code I'm trying to get to work

LET vStartDate = Num('2016-12-01');

LET vEndDate = Num(today());

temp_OpeningBalance:

LOAD

MonthStart($(vStartDate), IterNo()-1) as Date,

'0' AS Amount

AutoGenerate 1

While MonthStart($(vStartDate), IterNo()-1) <= $(vEndDate);

Join (temp_OpeningBalance)

Load Distinct

CompanyName,

Account,

CostingCode

Resident Fact;

temp_OpeningBalance:

Concatenate(temp_OpeningBalance)

Load

MonthStart(Date) AS MonthStartDate,

CompanyName,

Account,

CostingCode2,

SUM(Amount) AS SUM_Amount

Resident Fact

Group By MonthStartDate, CompanyName, Account, CostingCode;

OpeningBalance:

Load

MonthStartDate,

CompanyName,

Account,

CostingCode,

SUM_Amount,

if(Account = previous(Account) and CompanyName = previous(CompanyName) and CostingCode = previous(CostingCode) and Date = previous(Date),

RangeSum(SUM_Amount, peek('OB_Amount')), SUM_Amount) as OB_Amount

Resident temp_OpeningBalance

ORDER BY MonthStartDate, CompanyName, Account, CostingCode;

Re: RangeSum in loading script

udit_k — Wed, 05 Jul 2023 09:47:05 GMT

use previous function instead of Peek.

if(Account = previous(Account) and CompanyName = previous(CompanyName) and CostingCode = previous(CostingCode) and Date = previous(Date),

(SUM_Amount+Previous(SUM_Amount) ), SUM_Amount) as OB_Amount

Re: RangeSum in loading script

AJZ — Wed, 05 Jul 2023 11:24:09 GMT

Thanks for the quick reply.

However, this does not work. It just returns the same value as in the field SUM_Amount when selecting AccountCode and missing a lot of data when selecting other fields as well.

I need to sum up all preceeding months and this would just return the current + last value?

topic RangeSum in loading script in App Development

RangeSum in loading script

Re: RangeSum in loading script

Re: RangeSum in loading script