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!
