topic Re: Last 3 months sum , make as column value in script in QlikView

Last 3 months sum , make as column value in script

agni_gold — Tue, 04 Jul 2017 01:02:26 GMT

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.

Re: Last 3 months sum , make as column value in script

Tue, 04 Jul 2017 02:52:19 GMT

Hi Agnivesh..

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

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

Re: Last 3 months sum , make as column value in script

tresB — Tue, 04 Jul 2017 05:00:19 GMT

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;

Re: Last 3 months sum , make as column value in script

agni_gold — Wed, 05 Jul 2017 07:02:25 GMT

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

Re: Last 3 months sum , make as column value in script

tresB — Wed, 05 Jul 2017 07:09:26 GMT

Only change in previous reference, like:

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

Re: Last 3 months sum , make as column value in script

agni_gold — Wed, 05 Jul 2017 07:41:55 GMT

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 .

Re: Last 3 months sum , make as column value in script

tresB — Wed, 05 Jul 2017 07:45:36 GMT

May be this?

If ( RowNo()=TotalAmount,

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

) as RollingThree

Re: Last 3 months sum , make as column value in script

agni_gold — Wed, 05 Jul 2017 08:02:50 GMT

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

Re: Last 3 months sum , make as column value in script

agni_gold — Wed, 05 Jul 2017 08:25:48 GMT

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

Re: Last 3 months sum , make as column value in script

agni_gold — Wed, 05 Jul 2017 18:57:45 GMT

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