cummulative calculation in script

Anonymous — Wed, 05 Oct 2016 13:24:58 GMT

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

Re: cummulative calculation in script

sunny_talwar — Wed, 05 Oct 2016 13:34:03 GMT

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;

topic cummulative calculation in script in QlikView

cummulative calculation in script

Re: cummulative calculation in script