8 Replies Latest reply: Sep 20, 2016 6:49 AM by Ridhaa Hendricks RSS

    NetworkDays

    Ridhaa Hendricks

      Hi There

       

      I have an issue with the networkdays function in the way I am using it.

      When Subtracting  [Posted/Issue]  from Filedate it returns the difference which is what I need as the Age.

       

      In the Table below for Post issue date =2016/08/30 the age is 2 days yet the networkdays is 3.

      I need the networkdays to work accordingly to my age.

      Also I understand that networkdays in qlikview will not show negatives as a standard but in this case I need it to work accordingly with the age.

      I have attached the data and the model.

       

       

      Filedate

      Posted/Issue

      Age

      Networkdays

      NetworkdaysWithoutPublicholidays

      2016/09/01

      2016/07/30

      33

      24

      22

      2016/09/01

      2016/08/02

      30

      23

      21

      2016/09/01

      2016/08/05

      27

      20

      19

      2016/09/01

      2016/08/18

      14

      11

      11

      2016/09/01

      2016/08/22

      10

      9

      9

      2016/09/01

      2016/08/23

      9

      8

      8

      2016/09/01

      2016/08/24

      8

      7

      7

      2016/09/01

      2016/08/25

      7

      6

      6

      2016/09/01

      2016/08/26

      6

      5

      5

      2016/09/01

      2016/08/27

      5

      4

      4

      2016/09/01

      2016/08/29

      3

      4

      4

      2016/09/01

      2016/08/30

      2

      3

      3

      2016/09/01

      2016/08/31

      1

      2

      2

      2016/09/01

      2016/09/01

      0

      1

      1

      2016/09/01

      2016/09/02

      -1

      0

      0

      2016/09/01

      2016/09/03

      -2

      0

      0

      2016/09/01

      2016/09/05

      -4

      0

      0

      2016/09/01

      2016/09/06

      -5

      0

      0

      2016/09/01

      2016/09/07

      -6

      0

      0

       

       

      Regards,

        • Re: NetworkDays
          Anil Babu Samineni

          I don't have license on my machine

           

          Can you please share the script what you are tried for she, networking days, networking without public days so that might we help you

            • Re: NetworkDays
              Ridhaa Hendricks

              Thanks Anil.

               

               

              [Public Holidays]:

              LOAD Date(EventDate,'YYYY/MM/DD') as PublicHoildayDate, //[Pay Date],
                 // Date(Date,'MM/DD') as [Pay Date link],
                 [Public Holiday],
              'Hol'
              as Ind


              FROM
              [C:\Users\rhendricks\Desktop\Network Days\Public Holidays.xlsx]
              (
              ooxml, embedded labels, table is Sheet1);



              PubHolidays:
              Load Concat(Num(PublicHoildayDate),',') as PublicHolidays Resident [Public Holidays];
              LET customfirstworkdate='firstworkdate($1,$2,'&Peek('PublicHolidays') & ')';
              LET customnetworkdays  = 'networkdays($1,$2,' & peek('PublicHolidays') & ')';
              LET vPublicHolidays  = peek('PublicHolidays');



              Table1:
              LOAD
              [Posted/Issue],
              '2016/09/01'
              as Filedate
              FROM
              [C:\Users\rhendricks\Desktop\Network Days\Datefile.xlsx]
              (
              ooxml, embedded labels, table is export);


              Dates:
              Load *,
              Filedate-[Posted/Issue] as Age,
              NetWorkDays([Posted/Issue],Filedate) as Networkdays,
              NetWorkDays([Posted/Issue],Filedate,$(vPublicHolidays)) as NetworkdaysWithoutPublicholidays

              Resident Table1;
              DROP Table Table1;

            • Re: NetworkDays
              youyun Jupiter

              I guess there are 2 difference between Age = Date1 - Date2 and networkdays between Date1 and Date2.

              1. Age doesnt count Date2 (the start date), and so we are seeing lots of 1 day difference between Age and networkdays. If want to align Age and networkdays, may simply -1.

              2. Networkdays doesnt provide negative values. If file date before post/issue date, which value to be returned? may use If function to calculate separately.

              • Re: NetworkDays
                Sunny Talwar

                May be try this:

                 

                Dates:

                Load *,

                  Filedate-[Posted/Issue] as Age,

                  If([Posted/Issue] < Filedate, NetWorkDays([Posted/Issue], Filedate)-1, -NetWorkDays(Filedate, [Posted/Issue])+1) as Networkdays,

                  If([Posted/Issue] < Filedate, NetWorkDays([Posted/Issue], Filedate,$(vPublicHolidays))-1, -NetWorkDays(Filedate, [Posted/Issue],$(vPublicHolidays))+1) as NetworkdaysWithoutPublicholidays

                Resident Table1;

                DROP Table Table1;


                Capture.PNG