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;
