Range of Dates exclude Weekends

[#1]

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?

[#2]

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.

[#3]

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)