topic Networking days in QlikView

Networking days

Thu, 17 Nov 2011 13:51:22 GMT

Can we can apply the function networking days into a calendar by country and what is the strutucture of this file have to be.

Thanks,

Fernando.

Networking days

SunilChauhan — Thu, 17 Nov 2011 13:56:51 GMT

networkdays

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

networkdays (startdate, enddate, holidaydate1, holidaydate2,holidaydate3,holidaydate4.......)

for example

networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26')

hope this help

Networking days

SunilChauhan — Thu, 17 Nov 2011 13:57:53 GMT

also not include holidaydate(optional)

example

networkdays ('2006-12-18', '2006-12-31')

Re: Networking days

Thu, 17 Nov 2011 14:07:06 GMT

Thanks for your reply , but is possible apply the function networking days against one table with the working days?

Best Regards,

Fernando.

Networking days

swuehl — Thu, 17 Nov 2011 14:11:54 GMT

Should be possible, take a look at John's solution here:

http://community.qlik.com/message/131162

Re: Networking days

johnw — Fri, 18 Nov 2011 00:16:50 GMT

Having different holidays by country would be more complicated than what I did in that thread, though. Attached is one way to do it. Again, we're generating a holiday list in a variable, but now we generate a pick(match()) to select the right holiday list by country. I don't like that I have a separate load for each country, but I can't think of how to get around it. It probably won't be a problem in practice since the tables involved at that point are small.

I completely made up these holidays, so don't look for them to make any sense.

Here's the script:

[Holidays]:
LOAD * INLINE [
Country, Holiday
USA, 3/15/2011
USA, 3/10/2011
USA, 2/15/2011
USA, 1/1/2011
Mexico, 1/10/2011
Mexico, 2/15/2011
Mexico, 3/4/2011
];

Countries:
LOAD text(fieldvalue('Country',recno())) as Country
,recno() as Sequence
AUTOGENERATE fieldvaluecount('Country')
;
AllCountries:
LOAD concat(chr(39) & Country & chr(39),',',Sequence) as AllCountries
RESIDENT Countries
;
LET allholidays = '=pick(match(Country,' & peek('AllCountries') & ')'
;
FOR I = 1 TO noofrows('Countries')
    AllHolidays:
    LOAD concat(num(Holiday),',') as AllHolidays RESIDENT Holidays WHERE Country = peek('Country',$(I)-1,'Countries');
    LET allholidays = allholidays & ',' & chr(39) & peek('AllHolidays') & chr(39);
    DROP TABLE AllHolidays;
NEXT

LET allholidays = allholidays & ')';

DROP TABLES
Countries
,AllCountries
;

[Dates]:
LOAD * INLINE [
Date
03/05/2011
03/14/2011
03/17/2011
];

Then I made a list box with always one Country selected, and I used expressions like these.

firstworkdate(Date,4,$(allholidays)) // in a straight table by Date

networkdays('1/1/2011',min(Date),$(allholidays))

Re: Networking days

johnw — Fri, 18 Nov 2011 00:22:48 GMT

Heh. I meant to drop the Holiday table, then left it so you could see what the holidays actually were. But if you're leaving it in the data model, then there's a MUCH simpler way to handle things. Just define your variable like this:

=concat(num(Holiday),',')