Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
nickking
Contributor III
Contributor III

Target date 30 working days from date

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

Labels (1)
1 Solution

Accepted Solutions
em_bu
Contributor
Contributor

Hi Nick,

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

View solution in original post

5 Replies
igoralcantara
Partner Ambassador/MVP
Partner Ambassador/MVP

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;

 

 

 

igoralcantara_0-1715924001561.png

 

Check out my latest posts at datavoyagers.net
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

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.

Lech_Miszkiewicz_0-1715928835940.png

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
em_bu
Contributor
Contributor

Hi Nick,

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

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

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

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
nickking
Contributor III
Contributor III
Author

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