Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I'm trying to make a cummulative calculation in my QV-Script. (see last column in Excel).
I tried the following.
TMP_1:
NoConcatenate LOAD
KEY,
contractID,
yearmonth
sum(amount) as amount_PM //amount Per Month
Resident opbrengstplan_TMP
group by KEY,yearmonth
order by date asc;
TMP_2:
LOAD
KEY,
amount_PM
if(contractID=Previous(contractID) and yearmonth>Previous(yearmonth), NumSum(amount_PM, peek('cumAmount')), amount_PM) as cumAmount
Resident TMP_1;
what am I doing wrong?
Thanks in advance!
Sam
May be this:
opbrengstplan_TMP:
LOAD [contractID+yearmonth=KEY] as KEY,
ContractId,
YearMonth,
date,
bedragPM,
amount,
cummulative
FROM [example (7).xlsx]
(ooxml, embedded labels, table is Sheet1);
TMP_1:
LOAD KEY,
ContractId,
YearMonth,
Sum(amount) as amount_PM
Resident opbrengstplan_TMP
Group By KEY, ContractId, YearMonth;
Left Join (opbrengstplan_TMP)
LOAD KEY,
If(ContractId = Previous(ContractId) and YearMonth > Previous(YearMonth), RangeSum(amount_PM, Peek('cumAmount')), amount_PM) as cumAmount
Resident TMP_1
Order By ContractId, YearMonth;
DROP Table TMP_1;
May be this:
opbrengstplan_TMP:
LOAD [contractID+yearmonth=KEY] as KEY,
ContractId,
YearMonth,
date,
bedragPM,
amount,
cummulative
FROM [example (7).xlsx]
(ooxml, embedded labels, table is Sheet1);
TMP_1:
LOAD KEY,
ContractId,
YearMonth,
Sum(amount) as amount_PM
Resident opbrengstplan_TMP
Group By KEY, ContractId, YearMonth;
Left Join (opbrengstplan_TMP)
LOAD KEY,
If(ContractId = Previous(ContractId) and YearMonth > Previous(YearMonth), RangeSum(amount_PM, Peek('cumAmount')), amount_PM) as cumAmount
Resident TMP_1
Order By ContractId, YearMonth;
DROP Table TMP_1;