Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

Rolling 3 months in load script only

Hi ,

Need help to find rolling 3 months in load script only .

Tried this code  but  peek function gives different values for last 2 months than original column Amount.Time dimension starts from Jul 2015 but still see data for prior month where i expected it to be 0 .

Example: Nov 2015 , expected value is 379080491.56612+395828795.03718+373200829.17296

but summation is not fetching value expected as in screenshot below.

[IS_QTD]:

LOAD

       entity,

       time,

      Amount,

      RangeSum(Amount, Peek(Amount,-1), Peek(Amount, -2))     as '3M_Cumulative',

      RangeSum(Amount) as  [0 step],

      RangeSum(Alt(Peek('Amount',-1),0)) as [1 step],

      RangeSum(Alt(Peek('Amount',-2),0)) as [2 step],

     FROM IS_MTD;

Rolling3.PNG

Thanks,

Prithvi

Tags (1)
7 Replies

Re: Rolling 3 months in load script only

I think your data may not be ordered correctly which might be causing this issue. May be try to order your data in a resident load and then perform the accumulation.

Not applicable

Re: Rolling 3 months in load script only

Hi Sunny,

Thanks for response , but it gives completely different output .

Is  this query correct ?

Test:

Load

entity,

time,

Amount,

RangeSum(Amount, Peek(Amount,-1), Peek(Amount, -2))     as '3M_Cumulative',

RangeSum(Amount) as  [0 step],

Peek('Amount',-2,'IS_QTD') as [2 step],

Peek('Amount',-1,'IS_QTD') as [1 step]

RESIDENT  IS_QTD

ORDER BY  Amount asc;

rolling 3 months 2.PNG

Thanks,

Prithvi

Re: Rolling 3 months in load script only

Probably something like this

Test:

Load

entity,

time,

Amount,

RangeSum(Amount, Peek(Amount,-1), Peek(Amount, -2))    as '3M_Cumulative',

RangeSum(Amount) as  [0 step],

Peek('Amount',-2,'Incomestatement_QTD') as [2 step],

Peek('Amount',-1,'Incomestatement_QTD') as [1 step]

RESIDENT  IS_QTD

ORDER BY entity, time;

Not applicable

Re: Rolling 3 months in load script only

Hi,

it's still not working . same output as earlier one .Any other alternate to achive  rolling 3 months in load script ?

Thanks,

Prithvi

Re: Rolling 3 months in load script only

Other way would be to join the table three times, but I think the above way should work for you. Would you be able to share a sample with expected output?

Not applicable

Re: Rolling 3 months in load script only

Hi ,

PFA the excel  with sample data .

I need rolling  3 months for amount based on time and entity .

Thanks,

Prithvi

Re: Rolling 3 months in load script only

May be this?

Table:

LOAD Date(Date#(time, 'MMM YYYY'), 'MMM YYYY') as time,

  entity,

  Sum(Amount) as Amount

FROM [Sample (12).xlsx]

(ooxml, embedded labels, table is Sheet1)

Group By entity, time;

FinalTable:

LOAD *,

  If(entity = Previous(entity),

  If(entity = Previous(Previous(entity)),

  RangeSum(Previous(Previous(Amount)), Previous(Amount), Amount),

  RangeSum(Previous(Amount), Amount)),

  Amount) as [Rolling 3 months]

Resident Table

Order By entity, time;

DROP Table Table;

Capture.PNG

Community Browser