2 Replies Latest reply: Aug 17, 2010 11:18 AM by Khim Hoe Tan RSS

    Range of Dates exclude Weekends

      Greetings,

      I want to get the dates 5 days before the selected date. Currently I'm using the following expression:

       

      sum({$<Date={"<=$(=max(Date))>$(=date((max(Date)-5)))"}>} Amount)


      But I want to exclude Weekends (Saturday and Sunday) from the date range. I still want to keep the 5 days range. For example, if the user select a date that falls on Tuesday, it will include Wed, Thurs, Fri <Sat and Sun skipped> Mon, Tues. Any way to do this?

        • Range of Dates exclude Weekends
          Neil Miller

          Do you have any calendar fields (particularly Day of the Week) associated with the Date? If so, you can add some Set Analysis in your Max functions to exclude those dates.

          If you have a numerical Day of the Week field then something like:

          sum({$<Date={"<=$(=max({<DotW-={0,6}>}Date))
          >$(=date((max({<DotW-={0,6}>}Date, 5))))"}>} Amount)


          If you use the QlikView WeekDay function to add Day of the Week to your load, you could use something like:

          sum({$<Date={"<=$(=max({<DotW-={'Sun','Sat'}>}Date))
          >$(=date((max({<DotW-={'Sun','Sat'}>}Date, 5))))"}>} Amount)


          I changed the Max - 5 to use the second parameter of the Max function instead. Max(FIELD, 5) will pull the fifth highest value of FIELD. If you don't have all dates in your data set, then this may not work.

          You could also use an if/then inside your max functions, but Set Analysis would probably be preferred.

            • Range of Dates exclude Weekends

              Hi NMiller,

              I tried your suggestion, but it doesn't work for me. It seems that the second part ($(=date((max({<DotW-={'Sun','Sat'}>}Date, 5))))) of the set analysis is not returning any value.

              This is the other way that I worked on. Seems working fine:

               

              Sum ({<Day-={7,$(=if(Day=6,1,8))},Date={"<=$(=max(Date))>$(=date(max(Date)-6))"}>} Amount)


              *the requirement changed a bit... it only excludes Sundays. But if you want to exclude Sat and Sun, just change it to:

               

              Sum ({<Day-={7,6},Date={"<=$(=max(Date))>$(=date(max(Date)-7))"}>} Amount)