Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Is this only done where PL = PL001? or was that just for demonstration?
I need for all. Just mentioned for understanding what value should come each row
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;