Need Help in scripting

[Anonymous]

Hi Expert

I have data as below and want to create another field "P3Sales" it would have values of previous three months(including current month) group by FiscalYrMth, PL,CBN for all the months. I mentioned values for some rows  need for your refer.

Please help me to fix the issue

FiscalYrMth	PL	CBN	Sales	P3Sales
201401	PL001	CBN001	5	'=5
201401	PL001	CBN002	10	'=10
201401	PL002	CBN001	20
201401	PL002	CBN002	25
201401	PL003	CBN001	35
201401	PL003	CBN004	40
201402	PL001	CBN001	45	'=45+5'
201402	PL001	CBN002	50	'=50+10
201402	PL002	CBN001	60
201402	PL002	CBN002	65
201402	PL003	CBN001	75
201402	PL003	CBN004	80
201403	PL001	CBN001	85	'=85+45+5
201403	PL001	CBN002	90	'=90+50+10
201403	PL002	CBN001	100
201403	PL002	CBN002	105
201403	PL003	CBN001	115
201403	PL003	CBN004	120
201404	PL001	CBN001	125
201404	PL001	CBN002	130
201404	PL002	CBN001	140
201404	PL002	CBN002	145
201404	PL003	CBN001	155
201404	PL003	CBN004	160
201405	PL001	CBN001	165
201405	PL001	CBN002	170
201405	PL002	CBN001	180
201405	PL002	CBN002	185
201405	PL003	CBN001	195
201405	PL003	CBN004	200
201406	PL001	CBN001	205
201406	PL001	CBN002	210
201406	PL002	CBN001	220
201406	PL002	CBN002	225
201406	PL003	CBN001	235
201406	PL003	CBN004	240
201407	PL001	CBN001	245
201407	PL001	CBN002	250
201407	PL002	CBN001	260
201407	PL002	CBN002	265
201407	PL003	CBN001	275
201407	PL003	CBN004	280
201408	PL001	CBN001	285
201408	PL001	CBN002	290
201408	PL002	CBN001	300
201408	PL002	CBN002	305
201408	PL003	CBN001	315
201408	PL003	CBN004	320
201409	PL001	CBN001	325
201409	PL001	CBN002	330
201409	PL002	CBN001	340
201409	PL002	CBN002	345
201409	PL003	CBN001	355
201409	PL003	CBN004	360
201410	PL001	CBN001	365
201410	PL001	CBN002	370
201410	PL002	CBN001	380
201410	PL002	CBN002	385
201410	PL003	CBN001	395
201410	PL003	CBN004	400
201411	PL001	CBN001	405
201411	PL001	CBN002	410
201411	PL002	CBN001	420
201411	PL002	CBN002	425
201411	PL003	CBN001	435
201411	PL003	CBN004	440
201412	PL001	CBN001	445
201412	PL001	CBN002	450
201412	PL002	CBN001	460
201412	PL002	CBN002	465
201412	PL003	CBN001	475
201412	PL003	CBN004	480
201407	PL004	CBN005	100
201412	PL004	CBN005	200

[sunny_talwar]

Is this only done where PL = PL001? or was that just for demonstration?

[Anonymous]

I need for all. Just mentioned for understanding what value should come each row

[sunny_talwar]

May be try this:

Table:

LOAD RowNo() as Key,

  FiscalYrMth,

    PL,

    CBN,

    Sales

FROM

[https://community.qlik.com/thread/227748]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

  If(PL = Previous(Previous(PL)) and CBN = Previous(Previous(CBN)), RangeSum(Sales, Previous(Sales), Previous(Previous(Sales))),

  If(PL = Previous(PL) and CBN = Previous(CBN), RangeSum(Sales, Previous(Sales)), Sales)) as P3Sales

Resident Table

Order By PL, CBN, FiscalYrMth;

DROP Table Table;