Last 3 months sum , make as column value in script

[agni_gold]

Hi Friends,

I have date wise data and i want to write a script which generates a column in my script, which has sum of previous 3 months data for same month

ex : for apr 2016  , sum of amount for apr , mar and feb 2016.

[#2]

Hi Agnivesh..

It Can be done in front end also using rangesum and above funtion

RangeSum(Above((Sales),0,3))

[tresesco]

Try like:

t1:

LOAD Date,

    Month(Date) as Month,

    Year(Date) as Year,

    MonthName(Date) as MonthName,

    Amount,

    Amount_New

FROM

(ooxml, embedded labels, table is Sheet2);

t2:

Load

  MonthName,

  Sum(Amount) as TotalAmount

Resident t1 Group By MonthName Order By MonthName;

t3:

Load

  MonthName,

  TotalAmount,

  RangeSum(TotalAmount,Peek('TotalAmount'),Peek('TotalAmount',-2)) as RollingThree

Resident t2;

Drop Table t1,t2;

[agni_gold]

Thanks , Tresesco  ,

A little change in my requirement , if  i want like

For Apr month data i need to some (Mar+Feb+Jan)

and soo on

[tresesco]

Only change in previous reference, like:

RangeSum(Peek('TotalAmount'),Peek('TotalAmount',-2),Peek(TotalAmount,-3)) as RollingThree

[agni_gold]

Thanks, 

But for 1st month of whole data i am getting zero , can it be possible to have the same value comes in 1st month of whole data and rest should work same like above .

[tresesco · Accepted solution]

May be this?

If ( RowNo()=TotalAmount,

RangeSum(Peek('TotalAmount'),Peek('TotalAmount',-2),Peek(TotalAmount,-3))

)  as RollingThree

[agni_gold]

this is not working,

If(RowNo()=[Sales Plan in KS],RangeSum(Peek('Sales Plan in KS'),Peek('Sales Plan in KS',-2),Peek('Sales Plan in KS',-3))) as RollingThree_New_2

[agni_gold]

Thanks ,

I think , i was doing incorrect  ,  below is correct one .

  If(RowNo()=1,[Sales Plan in KS],RangeSum(Peek('Sales Plan in KS'),Peek('Sales Plan in KS',-2),Peek('Sales Plan in KS',-3))) as RollingThree_New_2

[agni_gold]

if i want to calculate last 6 and last 12 months also what should i change in expression ?