Target date 30 working days from date

[nickking]

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

[igoralcantara]

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;

[Lech_Miszkiewicz]

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.

[em_bu · Accepted solution]

Hi Nick,

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

[Lech_Miszkiewicz]

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

[nickking]

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