6 Replies Latest reply: Nov 17, 2011 7:22 PM by John Witherspoon RSS

    Networking days

      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
          Sunil Chauhan

          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
              Sunil Chauhan

              also not include holidaydate(optional)

               

              example

               

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

              • Re: Networking days

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

                 

                Best Regards,

                 

                Fernando.

                  • Networking days
                    Stefan Wühl

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

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

                      • Re: Networking days
                        John Witherspoon

                        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))