9 Replies Latest reply: Jul 28, 2016 6:14 AM by Vineeth Pujari RSS

    Excluding days in month

    Kshitija Shinde

      Hello everyone,

       

      How do i exclude 2nd and 4th saturday and all sundays in month??

       

      Thanks& Regards,

      Kshitija

        • Re: Excluding days in month
          Kaushik Solanki

          HI,

           

          Have a look at the link.

           

          NetWorkDays

           

          Regards,

          Kaushik Solanki

          • Re: Excluding days in month
            Sunny Talwar

            May be create flags for these dates in your script.:

             

            Table:

            LOAD Date,

              WeekDay(Date) as WeekDay,

              If(WeekDay(Date) = 'Sun' or (WeekDay(Date) = 'Sat' and Match(Div(Day(Date), 7), 1, 3)), 1, 0) as Exclude_Flag;

            LOAD Date(MakeDate(2016, 1, 1) + RecNo() - 1) as Date

            AutoGenerate 366;

            • Re: Excluding days in month
              Vineeth Pujari

              Add Flags to your Datefield or preferably in MasterCalendar as below

               

               

              Dates = is your datefield

               

              if(NUM(Dates) = makedate(YEar(Dates),NUM(Month(Dates)),1+7*2)-(NUM(WeekDay(MonthStart(Dates),6))+1),'2ndSat',

                    if(NUM(Dates) = makedate(YEar(Dates),NUM(Month(Dates)),1+7*4)-(NUM(WeekDay(MonthStart(Dates),6))+1),'4thSat')) as SaturdayFlag

              • Re: Excluding days in month
                Vineeth Pujari

                Can you share sample data?

                  • Re: Excluding days in month
                    Kshitija Shinde

                    Hi Vineeth ,

                     

                    I have attached sample data , i want to calculate the TAT of opening and closing date and then exclude 2nd and 4th Saturday and all Sundays.

                     

                    Thanks!

                      • Re: Excluding days in month
                        Vineeth Pujari

                        Had to write a module to get this result in Qlikview,

                         

                        I know you are using Qliksense but you can create a QVD using a personal edition and load that to your Qliksense app as a workaround until an someone has a better solution.

                         

                        LOGIC: use networkdays to exclude all saturdays & sundays between the period and then add the number of 1st or 3rd saturdays falling in the period

                         

                        MODULE:

                        Press CTRL + M and paste this code

                         

                        Function AdjustSaturdays(StartDate, EndDate)

                          Days = 0   

                           

                            For Dates = StartDate To EndDate

                           

                        ''    Check for Weeknumber to identify saturday of the month

                            IF (Day(Dates)/7)<=1 THEN

                          SatNumber = 1

                          ELSEIF (Day(Dates)/7)>1 and (Day(Dates)/7)<=2 THEN

                          SatNumber = 2

                         

                          ELSEIF (Day(Dates)/7)>2 and (Day(Dates)/7)<=3 THEN

                          SatNumber = 3

                         

                          ELSEIF (Day(Dates)/7)>3 and (Day(Dates)/7)<=4 THEN

                          SatNumber = 4

                         

                          ELSEIF (Day(Dates)/7)>4 and (Day(Dates)/7)<=5 THEN

                          SatNumber = 5

                         

                          ELSEIF (Day(Dates)/7)>5 and (Day(Dates)/7)<=6 THEN

                          SatNumber = 6

                         

                          END IF

                           

                           

                                If WeekDay(Dates) = 7  and (SatNumber = 1 or SatNumber = 3) THEN

                                    Days = Days + 1

                              End If

                         

                            Next

                            AdjustSaturdays = Days

                         

                        End Function

                         

                         

                        IN YOUR LOAD SCRIPT:

                         

                         

                        TATDAYS:

                        LOAD

                          Dates,

                          Stop,

                          NetworkDays,

                          SaturdaysToAdd,

                          NetworkDays + SaturdaysToAdd as TATDays;

                         

                         

                        LOAD Dates,

                            Stop,

                            NetWorkDays(Dates,Stop) as NetworkDays,

                        AdjustSaturdays(NUM(Dates),NUM(Stop)) as SaturdaysToAdd

                         

                         

                        FROM

                        [C:\Users\Vini\Documents\2nd4thSat.xlsx]

                        (ooxml, embedded labels, table is Sheet1);