Working Days in DD/MM/YYYY format

[Sumit2504]

Hi All,

I want to calculate No of working days in DD/MM/YYYY format.

For example - My starting date is 01/Jan/2020 and Last date is 31/Jan/2020 so i want list of all working days in DD/MMM/YYYY.

Regards,

Sumit

[tresB]

Do you have a date field? How do you want to see the result, in a table/KPI? Try to share a sample data and explain the expected output with respect to that. 

[Sumit2504]

 

I am expecting a solution similar to this one where I need to display working days in horizontal listboxor filterpane.

If I select a month then no of workings days it'll display.

[tresB]

Like this? 

If(Not Match(WeekDay(Date),'Sat', 'Sun'), Date) as WorkingDay;

[Sumit2504]

This solution looks fine, I need to exclude Holiday also which is available in text file or I can create inline load.

Can you please help me on that too

[tresB · Accepted solution]

Hi Sumit, Try like:

Map:
Mapping Load *, 1 as Dummy Inline [
Holiday
4/5/2021
4/6/2021
];

Load Date, Month(Date) as Month, Year(Date) as Year,
If(Not Match(WeekDay(Date),'Sat', 'Sun') And IsNull(ApplyMap('Map', Date,null())), Date) as WorkingDay;

Load
Date(Today()-356+RecNo()) as Date
AutoGenerate 750;

[Sumit2504]

It's working fine, thank you 

[Sumit2504]

Hi @tresB 

One more help, if I want to derive DD(Day) out of this derived workingDay field then can you help me.

As I am trying Day(workingDay) but it's throwing error.

[tresB]

You could get error, if you are wrote Day(workingDay) instead of Day(WorkingDay); you could get error if you try to use this field in the same or prior load where it is created. 

[Sumit2504]

Hi @tresB ,

It was a typo, I was using Day(Working Day)

Is there any way to create a day field out of derived field in same load script or if I need to create a new calculation to get list of working days in Day format ?