Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Nick,
The function lastworkdate() should do what you need: lastworkdate - script and chart function
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;
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.
Hi Nick,
The function lastworkdate() should do what you need: lastworkdate - script and chart function
funny - I didnt even think about looking at the built in functions 😄 well done @em_bu
I did look at the functions but failed to find the right one.