Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following table:
- CUSTOMER: It's the Customer ID.
- CONTRACT: Contract ID for Customer.
- UNIT: Physical Location for that Customer.
- PERIOD: Period for Transaction.
- AMOUNT: Amount for Transaction.
CUSTOMER | CONTRACT | UNIT | YEAR | MONTH | PERIOD | AMOUNT |
---|---|---|---|---|---|---|
3000333 | 12031 | LO076 | 2014 | jan | 201401 | 6974 |
3000333 | 12031 | LO076 | 2014 | feb | 201402 | 8153 |
3000333 | 12031 | LO076 | 2014 | mar | 201403 | 7967 |
3000333 | 12031 | LO076 | 2014 | apr | 201404 | 8118 |
3000333 | 12031 | LO076 | 2014 | may | 201405 | 8985 |
3000333 | 12031 | LO076 | 2014 | jun | 201406 | 10820 |
3000333 | 12031 | LO076 | 2014 | jul | 201407 | 10781 |
3000333 | 12031 | LO076 | 2014 | aug | 201408 | 10361 |
3000333 | 12031 | LO076 | 2014 | sep | 201409 | 8889 |
3000333 | 12031 | LO076 | 2014 | oct | 201410 | 8305 |
3000333 | 12031 | LO076 | 2014 | nov | 201411 | 10417 |
3000333 | 12031 | LO076 | 2014 | dec | 201412 | 17244 |
3000333 | 12031 | LO076 | 2015 | jan | 201501 | 13458 |
3000333 | 12031 | LO076 | 2015 | feb | 201502 | 9792 |
3000333 | 12031 | LO076 | 2015 | mar | 201503 | 13863 |
3000333 | 12031 | LO076 | 2015 | apr | 201504 | 14480 |
3000333 | 12031 | LO076 | 2015 | may | 201505 | 14800 |
3000333 | 12031 | LO076 | 2015 | jun | 201506 | 16356 |
3000333 | 12031 | LO076 | 2015 | jul | 201507 | 20282 |
3000333 | 12031 | LO076 | 2015 | aug | 201508 | 20507 |
3000333 | 21098 | LO076 | 2015 | sep | 201509 | 1348 |
3000333 | 12031 | LO076 | 2015 | sep | 201509 | 10242 |
3000333 | 12031 | LO076 | 2015 | oct | 201510 | 14399 |
3000333 | 12031 | LO076 | 2015 | nov | 201511 | 15975 |
3000333 | 12031 | LO076 | 2015 | dec | 201512 | 16541 |
3000333 | 12031 | LO076 | 2016 | jan | 201601 | 11211 |
3000333 | 12031 | LO076 | 2016 | feb | 201602 | 14775 |
3000333 | 12031 | LO076 | 2016 | mar | 201603 | 18849 |
3000333 | 12031 | LO076 | 2016 | apr | 201604 | 12901 |
3000333 | 12031 | LO076 | 2016 | may | 201605 | 5931 |
3000333 | 21098 | LO076 | 2016 | may | 201605 | 10640 |
3000333 | 21098 | LO076 | 2016 | jun | 201606 | 14807 |
3000333 | 21098 | LO076 | 2016 | jul | 201607 | 15912 |
3000333 | 21098 | LO076 | 2016 | aug | 201608 | 14639 |
3000333 | 21098 | LO076 | 2016 | sep | 201609 | 16358 |
3000333 | 21098 | LO076 | 2016 | oct | 201610 | 18262 |
3000333 | 21098 | LO076 | 2016 | nov | 201611 | 11917 |
3000333 | 21098 | LO076 | 2016 | dec | 201612 | 16524 |
3000333 | 21098 | LO076 | 2017 | jan | 201701 | 18094 |
3000333 | 21098 | LO076 | 2017 | feb | 201702 | 17321 |
3000333 | 21098 | LO076 | 2017 | mar | 201703 | 15631 |
3000333 | 21098 | LO076 | 2017 | apr | 201704 | 17193 |
3000333 | 21098 | LO076 | 2017 | may | 201705 | 3155 |
3000377 | 7381 | LO001 | 2014 | jan | 201401 | 93852 |
3000377 | 17228 | LO001 | 2014 | feb | 201402 | 2325 |
3000377 | 7381 | LO001 | 2014 | feb | 201402 | 61317 |
3000377 | 7381 | LO001 | 2014 | mar | 201403 | 78619 |
3000377 | 17228 | LO001 | 2014 | apr | 201404 | 38471 |
3000377 | 7381 | LO001 | 2014 | apr | 201404 | 39083 |
3000377 | 17228 | LO001 | 2014 | may | 201405 | 98469 |
3000377 | 17228 | LO001 | 2014 | jun | 201406 | 110237 |
3000377 | 17228 | LO001 | 2014 | jul | 201407 | 69946 |
3000377 | 17228 | LO001 | 2014 | aug | 201408 | 95158 |
3000377 | 17228 | LO001 | 2014 | sep | 201409 | 77824 |
3000377 | 17228 | LO001 | 2014 | oct | 201410 | 101601 |
3000377 | 17228 | LO001 | 2014 | nov | 201411 | 77811 |
3000377 | 17228 | LO001 | 2014 | dec | 201412 | 117653 |
3000377 | 17228 | LO001 | 2015 | jan | 201501 | 85369 |
3000377 | 17228 | LO001 | 2015 | feb | 201502 | 67573 |
3000377 | 17228 | LO001 | 2015 | mar | 201503 | 91392 |
3000377 | 17228 | LO001 | 2015 | apr | 201504 | 77619 |
3000377 | 17228 | LO001 | 2015 | may | 201505 | 97637 |
3000377 | 17228 | LO001 | 2015 | jun | 201506 | 111140 |
3000377 | 17228 | LO001 | 2015 | jul | 201507 | 107736 |
3000377 | 17228 | LO001 | 2015 | aug | 201508 | 111971 |
3000377 | 17228 | LO001 | 2015 | sep | 201509 | 84691 |
3000377 | 17228 | LO001 | 2015 | oct | 201510 | 107413 |
3000377 | 17228 | LO001 | 2015 | nov | 201511 | 107465 |
3000377 | 17228 | LO001 | 2015 | dec | 201512 | 139325 |
3000377 | 17228 | LO001 | 2016 | jan | 201601 | 77616 |
3000377 | 17228 | LO001 | 2016 | feb | 201602 | 75560 |
3000377 | 17228 | LO001 | 2016 | mar | 201603 | 90046 |
3000377 | 17228 | LO001 | 2016 | apr | 201604 | 102882 |
3000377 | 17228 | LO001 | 2016 | may | 201605 | 103817 |
3000377 | 17228 | LO001 | 2016 | jun | 201606 | 118126 |
3000377 | 17228 | LO001 | 2016 | jul | 201607 | 127735 |
3000377 | 17228 | LO001 | 2016 | aug | 201608 | 99631 |
3000377 | 17228 | LO001 | 2016 | sep | 201609 | 86894 |
3000377 | 17228 | LO001 | 2016 | oct | 201610 | 115902 |
3000377 | 17228 | LO001 | 2016 | nov | 201611 | 84059 |
3000377 | 17228 | LO001 | 2016 | dec | 201612 | 136959 |
3000377 | 17228 | LO001 | 2017 | jan | 201701 | 119856 |
3000377 | 17228 | LO001 | 2017 | feb | 201702 | 86674 |
3000377 | 17228 | LO001 | 2017 | mar | 201703 | 96927 |
3000377 | 17228 | LO001 | 2017 | apr | 201704 | 104974 |
3000377 | 17228 | LO001 | 2017 | may | 201705 | 11343 |
3000390 | 15155 | LO059 | 2014 | jan | 201401 | 5302 |
3000390 | 15155 | LO059 | 2014 | feb | 201402 | 7583 |
3000390 | 15155 | LO059 | 2014 | mar | 201403 | 5272 |
3000390 | 15155 | LO059 | 2014 | apr | 201404 | 5000 |
3000390 | 15155 | LO059 | 2014 | may | 201405 | 4445 |
3000390 | 15155 | LO059 | 2014 | jun | 201406 | 6297 |
3000390 | 15155 | LO059 | 2014 | jul | 201407 | 6633 |
3000390 | 15155 | LO059 | 2014 | aug | 201408 | 6925 |
3000390 | 15155 | LO059 | 2014 | sep | 201409 | 6683 |
3000390 | 15155 | LO059 | 2014 | oct | 201410 | 11084 |
3000390 | 15155 | LO059 | 2014 | nov | 201411 | 6673 |
3000390 | 15155 | LO059 | 2014 | dec | 201412 | 13716 |
3000390 | 15155 | LO059 | 2015 | jan | 201501 | 7323 |
3000390 | 15155 | LO059 | 2015 | feb | 201502 | 7414 |
3000390 | 15155 | LO059 | 2015 | mar | 201503 | 7075 |
3000390 | 15155 | LO059 | 2015 | apr | 201504 | 4535 |
3000390 | 15155 | LO059 | 2015 | may | 201505 | 4707 |
3000390 | 15155 | LO059 | 2015 | jun | 201506 | 4728 |
3000390 | 15155 | LO059 | 2015 | jul | 201507 | 6572 |
3000390 | 15155 | LO059 | 2015 | aug | 201508 | 8053 |
3000390 | 15155 | LO059 | 2015 | sep | 201509 | 8084 |
3000390 | 15155 | LO059 | 2015 | oct | 201510 | 11670 |
3000390 | 15155 | LO059 | 2015 | nov | 201511 | 6674 |
3000390 | 15155 | LO059 | 2015 | dec | 201512 | 17694 |
3000390 | 15155 | LO059 | 2016 | jan | 201601 | 7827 |
3000390 | 15155 | LO059 | 2016 | feb | 201602 | 10909 |
3000390 | 15155 | LO059 | 2016 | mar | 201603 | 12717 |
3000390 | 15155 | LO059 | 2016 | apr | 201604 | 7248 |
3000390 | 15155 | LO059 | 2016 | may | 201605 | 8891 |
3000390 | 15155 | LO059 | 2016 | jun | 201606 | 7717 |
3000390 | 22731 | LO059 | 2016 | jul | 201607 | 5795 |
3000390 | 15155 | LO059 | 2016 | jul | 201607 | 7173 |
3000390 | 22731 | LO059 | 2016 | aug | 201608 | 10487 |
3000390 | 22731 | LO059 | 2016 | sep | 201609 | 9374 |
3000390 | 22731 | LO059 | 2016 | oct | 201610 | 16361 |
3000390 | 22731 | LO059 | 2016 | nov | 201611 | 10804 |
3000390 | 22731 | LO059 | 2016 | dec | 201612 | 24861 |
3000390 | 22731 | LO059 | 2017 | jan | 201701 | 12224 |
3000390 | 22731 | LO059 | 2017 | feb | 201702 | 11260 |
3000390 | 22731 | LO059 | 2017 | mar | 201703 | 9259 |
3000390 | 22731 | LO059 | 2017 | apr | 201704 | 8822 |
3000390 | 22731 | LO059 | 2017 | may | 201705 | 2350 |
3000419 | 11405 | LO058 | 2014 | jan | 201401 | 4758 |
3000419 | 11405 | LO058 | 2014 | feb | 201402 | 4737 |
3000419 | 11405 | LO058 | 2014 | mar | 201403 | 7825 |
3000419 | 11405 | LO058 | 2014 | apr | 201404 | 9883 |
3000419 | 11405 | LO058 | 2014 | may | 201405 | 9455 |
3000419 | 11405 | LO058 | 2014 | jun | 201406 | 11516 |
3000419 | 11405 | LO058 | 2014 | jul | 201407 | 9184 |
3000419 | 11405 | LO058 | 2014 | aug | 201408 | 7262 |
3000419 | 11405 | LO058 | 2014 | sep | 201409 | 9862 |
3000419 | 11405 | LO058 | 2014 | oct | 201410 | 17847 |
3000419 | 11405 | LO058 | 2014 | nov | 201411 | 10454 |
3000419 | 11405 | LO058 | 2014 | dec | 201412 | 17267 |
3000419 | 11405 | LO058 | 2015 | jan | 201501 | 6602 |
3000419 | 11405 | LO058 | 2015 | feb | 201502 | 6599 |
3000419 | 11405 | LO058 | 2015 | mar | 201503 | 1297 |
3000419 | 19423 | LO058 | 2015 | mar | 201503 | 10221 |
3000419 | 19423 | LO058 | 2015 | apr | 201504 | 12869 |
3000419 | 19423 | LO058 | 2015 | may | 201505 | 13998 |
3000419 | 19423 | LO058 | 2015 | jun | 201506 | 10873 |
3000419 | 19423 | LO058 | 2015 | jul | 201507 | 9549 |
3000419 | 19423 | LO058 | 2015 | aug | 201508 | 10009 |
3000419 | 19423 | LO058 | 2015 | sep | 201509 | 14736 |
3000419 | 19423 | LO058 | 2015 | oct | 201510 | 20378 |
3000419 | 19423 | LO058 | 2015 | nov | 201511 | 12520 |
3000419 | 19423 | LO058 | 2015 | dec | 201512 | 23264 |
3000419 | 19423 | LO058 | 2016 | jan | 201601 | 7419 |
3000419 | 19423 | LO058 | 2016 | feb | 201602 | 7485 |
3000419 | 19423 | LO058 | 2016 | mar | 201603 | 13120 |
3000419 | 19423 | LO058 | 2016 | apr | 201604 | 14334 |
3000419 | 19423 | LO058 | 2016 | may | 201605 | 15509 |
3000419 | 19423 | LO058 | 2016 | jun | 201606 | 14698 |
3000419 | 19423 | LO058 | 2016 | jul | 201607 | 14876 |
3000419 | 19423 | LO058 | 2016 | aug | 201608 | 12356 |
3000419 | 19423 | LO058 | 2016 | sep | 201609 | 13574 |
3000419 | 19423 | LO058 | 2016 | oct | 201610 | 23320 |
3000419 | 19423 | LO058 | 2016 | nov | 201611 | 16664 |
3000419 | 19423 | LO058 | 2016 | dec | 201612 | 29297 |
3000419 | 19423 | LO058 | 2017 | jan | 201701 | 12086 |
3000419 | 19423 | LO058 | 2017 | feb | 201702 | 10899 |
3000419 | 19423 | LO058 | 2017 | mar | 201703 | 12277 |
3000419 | 19423 | LO058 | 2017 | apr | 201704 | 14683 |
3000419 | 19423 | LO058 | 2017 | may | 201705 | 3494 |
So I need to create a new field AMOUNT_LY, where I should get the AMOUNT from last year's period.
This would be the remaining result: (Note that AMOUNT_CY is the value for current year period)
Please, note that there might be multiple CONTRACTS for a CUSTOMER, as you could see.
CUSTOMER | PERIOD | AMOUNT_CY | AMOUNT_LY |
---|---|---|---|
3000333 | 201401 | 6974 | |
3000333 | 201402 | 8153 | |
3000333 | 201403 | 7967 | |
3000333 | 201404 | 8118 | |
3000333 | 201405 | 8985 | |
3000333 | 201406 | 10820 | |
3000333 | 201407 | 10781 | |
3000333 | 201408 | 10361 | |
3000333 | 201409 | 8889 | |
3000333 | 201410 | 8305 | |
3000333 | 201411 | 10417 | |
3000333 | 201412 | 17244 | |
3000333 | 201501 | 13458 | 6974 |
3000333 | 201502 | 9792 | 8153 |
3000333 | 201503 | 13863 | 7967 |
3000333 | 201504 | 14480 | 8118 |
3000333 | 201505 | 14800 | 8985 |
3000333 | 201506 | 16356 | 10820 |
3000333 | 201507 | 20282 | 10781 |
3000333 | 201508 | 20507 | 10361 |
3000333 | 201509 | 11590 | 8889 |
3000333 | 201510 | 14399 | 8305 |
3000333 | 201511 | 15975 | 10417 |
3000333 | 201512 | 16541 | 17244 |
3000333 | 201601 | 11211 | 13458 |
3000333 | 201602 | 14775 | 9792 |
3000333 | 201603 | 18849 | 13863 |
3000333 | 201604 | 12901 | 14480 |
3000333 | 201605 | 16571 | 14800 |
3000333 | 201606 | 14807 | 16356 |
3000333 | 201607 | 15912 | 20282 |
3000333 | 201608 | 14639 | 20507 |
3000333 | 201609 | 16358 | 11590 |
3000333 | 201610 | 18262 | 14399 |
3000333 | 201611 | 11917 | 15975 |
3000333 | 201612 | 16524 | 16541 |
3000333 | 201701 | 18094 | 11211 |
3000333 | 201702 | 17321 | 14775 |
3000333 | 201703 | 15631 | 18849 |
3000333 | 201704 | 17193 | 12901 |
3000333 | 201705 | 3155 | 16571 |
3000377 | 201401 | 93852 | |
3000377 | 201402 | 63642 | |
3000377 | 201403 | 78619 | |
3000377 | 201404 | 77554 | |
3000377 | 201405 | 98469 | |
3000377 | 201406 | 110237 | |
3000377 | 201407 | 69946 | |
3000377 | 201408 | 95158 | |
3000377 | 201409 | 77824 | |
3000377 | 201410 | 101601 | |
3000377 | 201411 | 77811 | |
3000377 | 201412 | 117653 | |
3000377 | 201501 | 85369 | 93852 |
3000377 | 201502 | 67573 | 63642 |
3000377 | 201503 | 91392 | 78619 |
3000377 | 201504 | 77619 | 77554 |
3000377 | 201505 | 97637 | 98469 |
3000377 | 201506 | 111140 | 110237 |
3000377 | 201507 | 107736 | 69946 |
3000377 | 201508 | 111971 | 95158 |
3000377 | 201509 | 84691 | 77824 |
3000377 | 201510 | 107413 | 101601 |
3000377 | 201511 | 107465 | 77811 |
3000377 | 201512 | 139325 | 117653 |
3000377 | 201601 | 77616 | 85369 |
3000377 | 201602 | 75560 | 67573 |
3000377 | 201603 | 90046 | 91392 |
3000377 | 201604 | 102882 | 77619 |
3000377 | 201605 | 103817 | 97637 |
3000377 | 201606 | 118126 | 111140 |
3000377 | 201607 | 127735 | 107736 |
3000377 | 201608 | 99631 | 111971 |
3000377 | 201609 | 86894 | 84691 |
3000377 | 201610 | 115902 | 107413 |
3000377 | 201611 | 84059 | 107465 |
3000377 | 201612 | 136959 | 139325 |
3000377 | 201701 | 119856 | 77616 |
3000377 | 201702 | 86674 | 75560 |
3000377 | 201703 | 96927 | 90046 |
3000377 | 201704 | 104974 | 102882 |
3000377 | 201705 | 11343 | 103817 |
3000390 | 201401 | 5302 | |
3000390 | 201402 | 7583 | |
3000390 | 201403 | 5272 | |
3000390 | 201404 | 5000 | |
3000390 | 201405 | 4445 | |
3000390 | 201406 | 6297 | |
3000390 | 201407 | 6633 | |
3000390 | 201408 | 6925 | |
3000390 | 201409 | 6683 | |
3000390 | 201410 | 11084 | |
3000390 | 201411 | 6673 | |
3000390 | 201412 | 13716 | |
3000390 | 201501 | 7323 | 5302 |
3000390 | 201502 | 7414 | 7583 |
3000390 | 201503 | 7075 | 5272 |
3000390 | 201504 | 4535 | 5000 |
3000390 | 201505 | 4707 | 4445 |
3000390 | 201506 | 4728 | 6297 |
3000390 | 201507 | 6572 | 6633 |
3000390 | 201508 | 8053 | 6925 |
3000390 | 201509 | 8084 | 6683 |
3000390 | 201510 | 11670 | 11084 |
3000390 | 201511 | 6674 | 6673 |
3000390 | 201512 | 17694 | 13716 |
3000390 | 201601 | 7827 | 7323 |
3000390 | 201602 | 10909 | 7414 |
3000390 | 201603 | 12717 | 7075 |
3000390 | 201604 | 7248 | 4535 |
3000390 | 201605 | 8891 | 4707 |
3000390 | 201606 | 7717 | 4728 |
3000390 | 201607 | 12968 | 6572 |
3000390 | 201608 | 10487 | 8053 |
3000390 | 201609 | 9374 | 8084 |
3000390 | 201610 | 16361 | 11670 |
3000390 | 201611 | 10804 | 6674 |
3000390 | 201612 | 24861 | 17694 |
3000390 | 201701 | 12224 | 7827 |
3000390 | 201702 | 11260 | 10909 |
3000390 | 201703 | 9259 | 12717 |
3000390 | 201704 | 8822 | 7248 |
3000390 | 201705 | 2350 | 8891 |
3000419 | 201401 | 4758 | |
3000419 | 201402 | 4737 | |
3000419 | 201403 | 7825 | |
3000419 | 201404 | 9883 | |
3000419 | 201405 | 9455 | |
3000419 | 201406 | 11516 | |
3000419 | 201407 | 9184 | |
3000419 | 201408 | 7262 | |
3000419 | 201409 | 9862 | |
3000419 | 201410 | 17847 | |
3000419 | 201411 | 10454 | |
3000419 | 201412 | 17267 | |
3000419 | 201501 | 6602 | 4758 |
3000419 | 201502 | 6599 | 4737 |
3000419 | 201503 | 11518 | 7825 |
3000419 | 201504 | 12869 | 9883 |
3000419 | 201505 | 13998 | 9455 |
3000419 | 201506 | 10873 | 11516 |
3000419 | 201507 | 9549 | 9184 |
3000419 | 201508 | 10009 | 7262 |
3000419 | 201509 | 14736 | 9862 |
3000419 | 201510 | 20378 | 17847 |
3000419 | 201511 | 12520 | 10454 |
3000419 | 201512 | 23264 | 17267 |
3000419 | 201601 | 7419 | 6602 |
3000419 | 201602 | 7485 | 6599 |
3000419 | 201603 | 13120 | 11518 |
3000419 | 201604 | 14334 | 12869 |
3000419 | 201605 | 15509 | 13998 |
3000419 | 201606 | 14698 | 10873 |
3000419 | 201607 | 14876 | 9549 |
3000419 | 201608 | 12356 | 10009 |
3000419 | 201609 | 13574 | 14736 |
3000419 | 201610 | 23320 | 20378 |
3000419 | 201611 | 16664 | 12520 |
3000419 | 201612 | 29297 | 23264 |
3000419 | 201701 | 12086 | 7419 |
3000419 | 201702 | 10899 | 7485 |
3000419 | 201703 | 12277 | 13120 |
3000419 | 201704 | 14683 | 14334 |
3000419 | 201705 | 3494 | 15509 |
Is there any way we could do this by script?
Thank you!
Hi
There are many solutions, but you have to explain a litter better what you want.
For example, the previous year but group by what? sumarize? or detail?
try to show the output that what do you want in each line to be easy to understand what you need
Fernando K.
Do you want the Amount_LY aggregated by customer, contract ?
Make a key of the Last Year
Customers:
LOAD
AutoNumberHash128(mid(PERIOD,1,4) -1 & mid(PERIOD,5,2),CUSTOMER,CONTRACT) as Key,
CUSTOMER,
CONTRACT,
UNIT,
YEAR,
MONTH,
PERIOD,
AMOUNT
// mid(PERIOD,1,4) -1 & mid(PERIOD,5,2) as LYrPeriod
FROM
(ooxml, embedded labels, table is Sheet1);
Left Join
Load
AutoNumberHash128(PERIOD,CUSTOMER, CONTRACT) as Key,
Sum(AMOUNT) as LYrAmount
Resident Customers
Group by PERIOD,CUSTOMER, CONTRACT;
Hi,
Script:
SET DateFormat='YYYYMM';
Data:
load *,Date#(PERIOD) as DDate inline
[
CUSTOMER,CONTRACT,UNIT,YEAR,MONTH,PERIOD,AMOUNT
3000419,19423,LO058,2017,feb,201702,10899
3000419,19423,LO058,2017,mar,201703,12277
3000419,19423,LO058,2017,apr,201704,14683
3000419,19423,LO058,2017,may,201705,3494
];
abc:
Load CUSTOMER,DDate,
sum(AMOUNT) as Amnt
Resident Data
group by CUSTOMER,DDate;
DROP Table Data;
xyz:
load *,Peek((Amnt),-12) as previous
Resident abc;
DROP Table abc;
---------------------------------------------------------------------------------------------------------------------------------
Dimension: CUSTOMER,DDate
Expression: Variable1=min({<DDate=> } year(DDate))
AMOUNT_CY =sum(Amnt)
AMOUNT_LY =if(Year(DDate)=$(Variable1),0,previous)
Regards,
Sanya Chauhan