Set Analysis Multiple Specific Dates

[Anonymous]

Could someone provide me with the correct syntax for set analysis to only look at the 6 previous Fridays?

I know I can do Date(Floor(ReloadTime()-7)), -14, -21, -28, -35, -42 but how do I combine all of those into one set analysis filter?

[maxgro]

sum ({$ <date={'$(=Date(Today()-7))','$(=Date(Today()-14))','$(=Date(Today()-21))'}>} Sales)

relace today() with floor(reloadtime())

[sunny_talwar]

Something along these lines:

=Sum({<Date = {'$(=WeekStart(Today(), 0, 4))', '$(=WeekStart(Today(), -1, 4))', '$(=WeekStart(Today(), -2, 4))', '$(=WeekStart(Today(), -3, 4))', '$(=WeekStart(Today(), -4, 4))', '$(=WeekStart(Today(), -5, 4))'}>} Sales)

UPDATE: You might have to play around with the third argument of the WeekStart function as in my script I have this variable SET FirstWeekDay=6; which might be different for you and you will need to adjust the $(=WeekStart(Today(), 0, 4) to make sure you get the Fridays.

[johnw]

I used your example to figure out a shorter expression using a loop. (Edit: changed to today(0). Today() is the date the document was opened, not that it was reloaded.)

sum({<Date={$(=concat(chr(39) & date(weekstart(today(0),0,4)+valueloop(0,-35,-7)) & chr(39),','))}>} Sales)

[sunny_talwar]

Super John‌

[swuehl]

You could create a master calendar:

The Master Calendar

Better Calendar Scripts | Qlikview Cookbook

Then it should boil down to something like

=Sum({<WeekDay = {'Fri'}, Date = {">$(=Date(Today()-42))"} >} Sales)

[Instead of Date field, you could also select in a sequential week number field]

You need to ensure that date format of Date field matches the set modifier date format and you may need to clear user selections in calendar fields that may interfere with the defined set. e.g. user selections in Week field (but also any other calendar field that may interfere):

=Sum({<WeekDay = {'Fri'}, Date = {">$(=Date(Today()-42))"}, Week= >} Sales)

[Anonymous]

I have a master calendar and this seems like the best approach but how do I make 'Fri' current weekday?  WeekDay = {'=WeekDay(ReloadTime())'} ?

[swuehl]

  WeekDay = {'$(=WeekDay(ReloadTime()))'} ?

[Anonymous]

Trying this now but the average calculation is different than what I calculated in Excel.  Off my 1's and 2's on every hour. 

How can I exclude current date in this formula?

[swuehl · Accepted solution]

Not sure if I understand what you are saying.

Could you elaborate on this?

And maybe post a small sample QVW or the expressions you are using with some sample input records and your excel calculation?

edit:

If you want to exclude current date:

=Sum({<WeekDay = {'Fri'}, Date = {">$(=Date(Today()-42))<$(=Today())"} >} Sales)

Maybe you need to adjust the lower limit to >= then, to get 6 dates in total.