topic Re: Target date 30 working days from date in App Development

Target date 30 working days from date

nickking — Thu, 16 May 2024 15:50:53 GMT

Hi,

I have several measures where I need to identify if an action happened a specified number of working days after a supplier was instructed. Just adding a fixed number of days to a date doesn't take into account the differing lengths of each month. So rather than calculating the number of networkdays between two dates I need to add 30 networkdays to a date to get the target date.

Does anyone have any idea how I might achieve this?

Thanks

Nick

Re: Target date 30 working days from date

igoralcantara — Fri, 17 May 2024 05:33:29 GMT

Hi Nick,

This is a fun challenge. Here is one example of how you can do it. Attached an image of the data model after this example. Adapt to your use case.

// Load holidays into a table
// This is an Inline table as an example. You can store elsewhere and load from there
// I added my birthday and towel day just for fun
Holidays:
LOAD
Date#(HolidayDate, 'YYYY-MM-DD') as HolidayDate
INLINE [
HolidayDate
2024-01-01
2024-05-07
2024-05-25
2024-07-04
2024-11-28
2024-12-25
];

// Create a transactional table
Transactions:
LOAD * INLINE [
TransactionID, TransactionDate, Amount
1, 2024-01-02, 100
2, 2024-02-15, 150
3, 2024-03-10, 200
4, 2024-04-20, 250
5, 2024-05-30, 300
];

// Generate a series of dates for each transaction
TempDates:
LOAD
TransactionID,
IterNo() AS DayIncrement,
Date(TransactionDate + IterNo() - 1) AS TempDate
RESIDENT Transactions
WHILE IterNo() <= 60; // Generate enough days to cover 30 working days

// Filter out weekends and holidays
FilteredDates:
LOAD
TransactionID,
TempDate AS WorkDate
RESIDENT TempDates
WHERE Not Match(Num(WeekDay(TempDate)), 5, 6) // Weekdays only
AND NOT Exists(HolidayDate, TempDate); // Exclude holidays

// Add a row number to each workday per transaction
RankedWorkdays:
LOAD
TransactionID,
WorkDate,
If(Peek('TransactionID') = TransactionID, Peek('WorkdayRank') + 1, 1) AS WorkdayRank
RESIDENT FilteredDates
ORDER BY TransactionID, WorkDate;

// Select the workday that is 30 days from the transaction date
Left Join(Transactions)
LOAD
TransactionID,
WorkDate AS TargetDate
RESIDENT RankedWorkdays
WHERE WorkdayRank = 30;

// Drop temporary tables
DROP TABLE TempDates;
DROP TABLE FilteredDates;
DROP TABLE RankedWorkdays;

Re: Target date 30 working days from date

Lech_Miszkiewicz — Fri, 17 May 2024 07:07:06 GMT

Attached is a script which autogenerates mapping table. You can just adjust range of dates required and use it for mapping

Cheers

// Create variable with public holidays (Uncomment and update accordingly) // Holidays: // Load // Concat(Distinct Date(HolidayDate),',') as HolidayDates // FROM // [your holiday table] // ; // Let vHolidays = Peek('HolidayDates',0,'Holidays'); // Drop Table Holidays; // from 2020 to End of next year // adjust to your liking Let start_date = Floor(Makedate(2020,1,1)); let end_date = Floor(YearEnd(Today(),1)); temp_calendar: Load Floor($(start_date) + Rowno()) as date_date ,Floor($(start_date) + Rowno()) as temp_date AutoGenerate ($(end_date)-$(start_date)) ; // create empty table map_30_networkdays_temp: Load date(1) as from ,date(1) as to AutoGenerate 0 ; // Loop through dates and create mapping of from-to dates which are createing 30 networkdays combination For each v_date in FieldValueList('temp_date') Trace v_date is: $(v_date); NoConcatenate temp_map: Load Distinct temp_date as from_temp ,temp_date+Iterno()-1 as to_temp ,NetWorkDays((temp_date),(temp_date+Iterno()-1)) as networkdays Resident temp_calendar While NetWorkDays(Date((temp_date)),Date(temp_date+Iterno()-1)) <=30 and temp_date=$(v_date) ; // //NetWorkDays((temp_date),(temp_date+Iterno()-1),$(vHolidays)) // That is where $(vHolidays)needs to go // Add record to mapping table Concatenate (map_30_networkdays_temp) Load date(from_temp) as from ,date(to_temp) as to Resident temp_map Where networkdays = 30 ; Drop Table temp_map; Next // Drop table Drop table temp_calendar; // Final mapping table needs to be filtered to use first day meeting 30 networkdays requirement // Use this map to map your date to "min_to" date in your app (uncomment "Mapping" and applymap in your data script) //Mapping map_30_networkdays: Load from ,date(min(to)) as min_to Resident map_30_networkdays_temp Group by from ; Drop table map_30_networkdays_temp; exit script;

Result is a mapping table with dates "from" and "min_to".

If you have publicholiday calendar you need to add array of public holidays to the networday function in the script.

Re: Target date 30 working days from date

em_bu — Fri, 17 May 2024 08:34:20 GMT

Hi Nick,

The function lastworkdate() should do what you need: lastworkdate - script and chart function

Re: Target date 30 working days from date

Lech_Miszkiewicz — Fri, 17 May 2024 09:13:51 GMT

funny - I didnt even think about looking at the built in functions 😄 well done @em_bu

Re: Target date 30 working days from date

nickking — Fri, 17 May 2024 09:22:20 GMT

I did look at the functions but failed to find the right one.