Amien wrote:holiday can also be a table? or need they be in the expression?
I'm not sure. I've never used it. I would HOPE that you could define a field that holds all of your holidays, and then just put the field in the function, but I don't know if you can, and the examples show dates listed explicitly in the expression. If not, maybe there's some way to use concat(HolidayDate,',') to build the list, and somehow trick QlikView into doing a $ expansion or in some other way think you've explicitly listed them all.
There MUST be a way to do it without needing to write every single holiday date in the expression. That's just tedious.
My first thought is to do a count of holiday days in the date range and subtract them, but that can't work because you don't know the date range UNTIL you've accounted for the holiday days.
My next thought was to generate your own "function" to do it from your holiday table. OK, that seems to work. Build the function like this:
[Holidays]: LOAD * INLINE [ Holiday 3/15/2010 3/10/2010 ]; SET customfirstworkdate = 'firstworkdate($1,$2'; FOR I = 0 TO noofrows('Holidays')-1 LET customfirstworkdate = customfirstworkdate & ',' & num(peek('Holiday',I,'Holidays')); NEXT I LET customfirstworkdate = customfirstworkdate & ')';
And then use it like this:
$(customfirstworkdate(EndDate,NoOfWorkdays))
See attached.
There might be a better way. But at least you won't have to manually maintain the list of holidays. You can just load it from the normal source of the data.
When you're providing values for the parameters, it's using commas as the separator. Since it is a dollar sign expansion, that part is occurring before anything else. So I believe your second parameter is getting prematurely cut off right before the formatting, which would keep it from working.
I don't THINK you need to keep a consistent format for the networkdays function, as long as it's a date or a number that can be interpreted as a date. So the easy solution would be to remove the formatting and just use this:
$(customnetworkdays('1-1-2010',min(datevalue)))
Does that work? If not, I can add a customnetworkdays "function" to my sample file and try to figure out better what's going on.
