Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I am trying to create a field "Days Aged" in data load editor OR expression ,which is based on Date field , if dates are consecutive week then "Days Aged " starts with 0 and then + 7 within Vendor ID and Item.
In the sample data below in the table , I have 4 combinations of Vendor ID and Item:
1- ABC - 1 2- DEF -1 3- DEF - 2 4- XYZ - 2
Vendor ID | Item | Date | Expected Field -Days Aged | |
ABC | 1 | Sept 1 | 0 | Combination 1 |
ABC | 1 | Sept 8 | 7 | |
ABC | 1 | Sept 15 | 14 | |
ABC | 1 | Sept 22 | 21 | |
DEF | 1 | Aug 4 | 0 | Combination 2 |
DEF | 1 | Aug 11 | 7 | |
DEF | 1 | Aug 18 | 14 | |
DEF | 1 | Aug 25 | 21 | |
DEF | 1 | Sept 1 | 28 | |
DEF | 2 | Sept 15 | 0 | Combination 3 |
XYZ | 2 | May 5 | 0 | Combination 4 |
XYZ | 2 | June 16 | 0 | |
XYZ | 2 | July 16 | 0 |
below is the detail Why I am getting the "Days Aged" values of each Vendor ID and Item(combination):
1- ABC - 1
Expected field "Days Aged" = for Sept 1 = 0 because there is no previous week data
for Sept 8 = 7 Because it has previous data of last week so 0+7=7
For Sept 15 = 14 Because it has previous data of last week so 7+7 = 14
For Sept 22 = 21 Because it has previous data of last week so 14 + 7 = 21
2- DEF - 1
Expected field "Days Aged" = for Aug 4 = 0 because there is no previous week data
for Aug 11 = 7 Because it has previous data of last week so 0+7=7
For Aug 18 = 14 Because it has previous data of last week so 7+7 = 14
For Aug 25 = 28 Because it has previous data of last week so 14+7 = 21
For Sept = 1 Because it has previous data of last week so 21+7 = 28
3- DEF - 2
Expected field "Days Aged" = for Sept 15 = 0 because there is no previous week data
4- XYZ - 2
Expected field "Days Aged" = for May 5 = 0 because there is no previous week data
for June 16 = 0 because there is no previous week data / no consecutive week data
For July 16 = 0 because there is no previous week data / no consecutive week data
I hope i have made the requirement clear enough.
Any suggestion or tips will be appreciated as i need to get this done quickly.
Thanks!