Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ramusubramani
New Contributor II

Need Help in scripting

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

  

FiscalYrMthPLCBNSalesP3Sales
201401PL001CBN0015'=5
201401PL001CBN00210'=10
201401PL002CBN00120
201401PL002CBN00225
201401PL003CBN00135
201401PL003CBN00440
201402PL001CBN00145'=45+5'
201402PL001CBN00250'=50+10
201402PL002CBN00160
201402PL002CBN00265
201402PL003CBN00175
201402PL003CBN00480
201403PL001CBN00185'=85+45+5
201403PL001CBN00290'=90+50+10
201403PL002CBN001100
201403PL002CBN002105
201403PL003CBN001115
201403PL003CBN004120
201404PL001CBN001125
201404PL001CBN002130
201404PL002CBN001140
201404PL002CBN002145
201404PL003CBN001155
201404PL003CBN004160
201405PL001CBN001165
201405PL001CBN002170
201405PL002CBN001180
201405PL002CBN002185
201405PL003CBN001195
201405PL003CBN004200
201406PL001CBN001205
201406PL001CBN002210
201406PL002CBN001220
201406PL002CBN002225
201406PL003CBN001235
201406PL003CBN004240
201407PL001CBN001245
201407PL001CBN002250
201407PL002CBN001260
201407PL002CBN002265
201407PL003CBN001275
201407PL003CBN004280
201408PL001CBN001285
201408PL001CBN002290
201408PL002CBN001300
201408PL002CBN002305
201408PL003CBN001315
201408PL003CBN004320
201409PL001CBN001325
201409PL001CBN002330
201409PL002CBN001340
201409PL002CBN002345
201409PL003CBN001355
201409PL003CBN004360
201410PL001CBN001365
201410PL001CBN002370
201410PL002CBN001380
201410PL002CBN002385
201410PL003CBN001395
201410PL003CBN004400
201411PL001CBN001405
201411PL001CBN002410
201411PL002CBN001420
201411PL002CBN002425
201411PL003CBN001435
201411PL003CBN004440
201412PL001CBN001445
201412PL001CBN002450
201412PL002CBN001460
201412PL002CBN002465
201412PL003CBN001475
201412PL003CBN004480
201407PL004CBN005100
201412PL004CBN005200
3 Replies

Re: Need Help in scripting

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

ramusubramani
New Contributor II

Re: Need Help in scripting

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

Re: Need Help in scripting

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;

Community Browser