10 Replies Latest reply: Mar 7, 2016 10:32 AM by Nick Listerman

# Set Analysis Multiple Specific Dates

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?

• ###### Re: Set Analysis Multiple Specific Dates

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

relace today() with floor(reloadtime())

• ###### Re: Set Analysis Multiple Specific Dates

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.

• ###### Re: Set Analysis Multiple Specific Dates

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)

• ###### Re: Set Analysis Multiple Specific Dates

Super John

• ###### Re: Set Analysis Multiple Specific Dates

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)

• ###### Re: Set Analysis Multiple Specific Dates

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

• ###### Re: Set Analysis Multiple Specific Dates

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

• ###### Re: Set Analysis Multiple Specific Dates

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?

• ###### Re: Set Analysis Multiple Specific Dates

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.

• ###### Re: Set Analysis Multiple Specific Dates

The updated set analysis to exclude today fixed my average differences.  Thanks everyone!