3 Replies Latest reply: Apr 24, 2013 6:31 PM by Stefan Wühl RSS

    Out of office

    Robin Tappij Gielen

      Hi,

       

      How can I filter on data to show only data out of office.

       

      For example:

       

      Working time:     Ma - Fri     08:00 - 22.00 hrs

      Not working:       outside Working time, incl. full weekend

       

      I'll tried this below for example in a script, but is isn't working:

        if(Num(WeekDay(Date))>=0 and Num(WeekDay(Date))<=4 and Num(Time)>=Num('08:00:00') and Num(Time)<=Num('22:00:00'), 1, 0) as worktime,
        if(Num(WeekDay(Date))>=0 and Num(Time)<=Num('08:00:00') and Num(Time)>=Num('22:00:00'), 1, 0) as noworking

       

      Regards, Tappi

        • Re: Out of office

          Tappi, have you tried the intervalmatch() function? It sounds like exactly what you need.

           

          This post http://community.qlik.com/docs/DOC-3557 has more info.

            • Re: Out of office
              Robin Tappij Gielen

              Hi,

               

              Thanks for the answer, but somehow I don't understand it how to use this within my example.

               

              I want to use a selection field, for example:  "worktime" or "noworking".

                • Re: Out of office
                  Stefan Wühl

                  Num() function is used to format a number, but you use a literal as argument in e.g. num('08:00:00').

                   

                  If you want to parse your literal as a time, use a date/time interpretation function:

                   

                  time#('08:00:00','hh:mm:ss')

                   

                  or just maketime(8)

                   

                  If you change your code using interpretation functions, I think your code should work, assuming that Date and Time fields are read by QV as such, date and time, i.e. both should have a numeric representation. If you are in doubt, num(Date) and num(Time) should return numbers (Date large numbers like 41234 and Time smaller than 1).

                  If they don't return numbers, use time#(Time,'FORMATCODE') and date#(Date,'FORMATCODE') when reading in your data from the source.

                   

                  Hope this helps,

                  Stefan