cummulative calculation in script

[Anonymous]

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

[sunny_talwar · Accepted solution]

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;