Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jason_nicholas
Creator II
Creator II

Calculating NetWorkDays per month and per country

I have been reviewing this thread (Find Net Working Days) and others to resolve this issue, but I can't quite understand it enough to transfer it to my own purposes. Hopefully, you can help clear up the confusion

My data comes in as monthly totals. I don't have individual dates included in my data at all, and the two relevant pieces of data are [Month] and [Region]. I need to calculate the number of working days per month per region.

I will have a source table in an Excel file listing all of the holidays (shortened here to save space) which will take the place of the Inline Load in the prior thread's example. Rather than having a single holiday list to use, I will need to use one of these four depending on the data that comes in [Region].

   

US HolidaysUK HolidaysHong Kong HolidaysIndia Holidays
2-Jan2-Jan2-Jan26-Jan
16-Jan14-Apr30-Jan24-Feb
20-Feb17-Apr31-Jan

13-Mar

My source data is a sum of [Hours] executed by a specific resource in a given month. That resource is in a specific region, and I need to compare the value [Hours] to the available hours (number of work days * 8 hour shift). So my end function needs to look at a given month, count the Monday through Friday days and subtract the number of holidays in that month, based on the region of the resource.

ResourceRegionMonthHours
Resource1USJanuary150
Resource2UKFebruary162
Resource3USFebruary148

Where I get lost in the linked thread's example is in the table tmpConcat. I can't quite get my head around what is happening here. If I replace the inline table tmpHoliday with my own Excel table with the four holiday lists, what do I need to add to my code in order to use the [Month] and [Region] fields to determine the correct number of working days?

Normally, I would try to load it as it is and watch what happens when I make changes. However, this is a new application in development, and I really can't get anything out of it to test without this most basic function. I figured I would get some guidance before diving in to set me on the right track.

2 Replies
jason_nicholas
Creator II
Creator II
Author

I've figured out tmpConcat. It creates a comma-delimited list of holiday dates, to be used in the third value of the NetWorkDays function. It's more obvious than I was making it.

That leads me to believe that I can just use the code as it is (with my replacement holiday table) and that if I build a straight table chart with Month and Region as dimensions and a NetWorkDays function as the expression, I would get a table listing the number of work days each month and each year.

However, what I need for my application is a variable, which chooses the appropriate value for number of days to be used in a different formula; not the table itself. If I haven't made a mistake to this point, I think the help I need is with creating that variable.

specter21
Contributor
Contributor

I have a similar ask where I want to find network days based on attendance tracker for each employee..

 

Please help.