Announcements
cancel
Showing results for
Did you mean:
Creator

## How to check Consecutive dates ?

Hi All,

I got issue to check the number of consecutive dates of each Vendor Code and its Items.

In my dataset, Vendor Code , Item and Date are dimensions.

 Vendor ID Item Date ABC 1 Sept 1 ABC 1 Sept 8 ABC 1 Sept 15 ABC 1 Sept 22 DEF 1 Aug 4 DEF 1 Aug 11 DEF 1 Aug 18 DEF 1 Aug 25 DEF 1 Sept 1 DEF 2 Sept 15 XYZ 2 May 5 XYZ 2 June 16 XYZ 2 July 16

Expected Result:

Calculate the Days Aged Field values , Check if its Date field is consecutive week dates within Vendor ID and Item.

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.

the result should be like:

 Vendor ID Item Date Expected Field(Days Aged ) Explanation of Days Aged Values ABC 1 Sept 1 0 this Vendor Code and Item has consecutive weeks , Sept 1 has no previous week data 0 then +7 days to below dates. ABC 1 Sept 8 7 ABC 1 Sept 15 14 ABC 1 Sept 22 21 DEF 1 Aug 4 0 this Vendor Code and Item has consecutive weeks , Aug 4 has no previous week data 0 then +7 days to below dates. 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 this Vendor Code and Item  Sept 15 has no previous data and no consecutive weeks data. XYZ 2 May 5 0 this Vendor Code and Item has no consecutive weeks data, May 5 , June 16, and July 16 are not consecutive weeks so Days aged = 0 XYZ 2 June 16 0 XYZ 2 July 16 0

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!

Labels (6)

• ### Visualization

1 Solution

Accepted Solutions
Creator II

original_table:
*
inline [
Vendor ID, Item, Date , new_date, Expected Field(Days Aged )
ABC, 1, Sept 1, 9/1/2023, 0
ABC, 1, Sept 8, 9/8/2023, 7
ABC, 1, Sept 15, 9/15/2023, 14
ABC, 1, Sept 22, 9/22/2023, 21
DEF, 1, Aug 4, 8/4/2023, 0
DEF, 1, Aug 11, 8/11/2023, 7
DEF, 1, Aug 18, 8/18/2023, 14
DEF, 1, Aug 25, 8/25/2023, 21
DEF, 1, Sept 1, 9/1/2023, 28
DEF, 2, Sept 15, 9/15/2023, 0
XYZ, 2, May 5, 5/5/2023, 0
XYZ, 2, June 16, 6/16/2023, 0
XYZ, 2, July 16, 7/16/2023, 0
]
;

noconcatenate
final_table:
*,
if([Vendor ID] = previous([Vendor ID]) and Item = previous(Item) and date(new_date - 7) = previous(new_date),
RangeSum(peek('DaysAged'),7), 0) AS DaysAged
resident original_table
order by [Vendor ID], Item, Date;

drop table original_table;

4 Replies
Creator II

original_table:
*
inline [
Vendor ID, Item, Date , new_date, Expected Field(Days Aged )
ABC, 1, Sept 1, 9/1/2023, 0
ABC, 1, Sept 8, 9/8/2023, 7
ABC, 1, Sept 15, 9/15/2023, 14
ABC, 1, Sept 22, 9/22/2023, 21
DEF, 1, Aug 4, 8/4/2023, 0
DEF, 1, Aug 11, 8/11/2023, 7
DEF, 1, Aug 18, 8/18/2023, 14
DEF, 1, Aug 25, 8/25/2023, 21
DEF, 1, Sept 1, 9/1/2023, 28
DEF, 2, Sept 15, 9/15/2023, 0
XYZ, 2, May 5, 5/5/2023, 0
XYZ, 2, June 16, 6/16/2023, 0
XYZ, 2, July 16, 7/16/2023, 0
]
;

noconcatenate
final_table:
*,
if([Vendor ID] = previous([Vendor ID]) and Item = previous(Item) and date(new_date - 7) = previous(new_date),
RangeSum(peek('DaysAged'),7), 0) AS DaysAged
resident original_table
order by [Vendor ID], Item, Date;

drop table original_table;

Creator
Author

In my data set I have 3 fields Vendor ID , Item and Date (check my first table). my Expected result will be the 4th calculated field "Days Aged" in my table1 and sorry in my actual data set my date format is already like  10/2/2023.

In your Inline table I can see the "Days Aged" field. does your code work with my  table 1 dataset?

Thanks.

Creator II

I added new_date to make the sorting work. If it is already in your data in that format (10/2/2023), it should work and the new_date is unnecessary.

My inline table loaded "Expected Field (Days Aged)" to check against the calculated "DaysAged" in the second table in my script. The DaysAged calculation should work with your table. It technically references itself in the rangesum function, but it doesn't have to exist before this table to work correctly.

Let me know if I can clarify anything.

Creator
Author

No worries , it is working! Thanks I really appreciated :).