Making use of Date functions

Modified on Tue, 05 Mar 2024 at 09:12 PM

1. Create custom aging values from date dimension w.r.t today’s date

Description:

Define custom aging values (0-60 days, 60-90 days, etc.) based on two date ranges. The date ranges can either any two dates present in the data, or any single date column (in this case it is ‘Stock Date’) with respect to today’s date.

Example:

As per the above figure, ‘Item1’ having batch ‘BT003’ is in stock since 1^stJan2021. Hence, we can say that it is in stock from more than 61 days and less than 120 days considering today’s date as 26^th Apr 2021.

UDDC Expression:

ifCase( (dateDiff( "d", today(), StockDate)) >= 0 &&(dateDiff( "d", today(), StockDate)) <=60, "0 to 60 Days",

ifCase( (dateDiff( "d", today(), StockDate)) >= 61 &&(dateDiff( "d", today(), StockDate)) <=120, "61 to 120 Days",

ifCase( (dateDiff( "d", today(), StockDate)) >= 121 &&(dateDiff( "d", today(), StockDate)) <=180, "121 to 180 Days",

ifCase( (dateDiff( "d", today(), StockDate)) >= 181 &&(dateDiff( "d", today(), StockDate)) <=365, "181 to 365 Days","More than 365 Days"))))