topic Re: Set Analysis Multiple Specific Dates in QlikView

Set Analysis Multiple Specific Dates

Anonymous — Fri, 04 Mar 2016 19:51:37 GMT

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

maxgro — Fri, 04 Mar 2016 19:58:54 GMT

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

relace today() with floor(reloadtime())

Re: Set Analysis Multiple Specific Dates

sunny_talwar — Fri, 04 Mar 2016 23:26:10 GMT

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

johnw — Sat, 05 Mar 2016 00:03:00 GMT

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

sunny_talwar — Sat, 05 Mar 2016 00:11:47 GMT

Super John‌

Re: Set Analysis Multiple Specific Dates

swuehl — Sat, 05 Mar 2016 20:36:08 GMT

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

Anonymous — Mon, 07 Mar 2016 14:11:58 GMT

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

swuehl — Mon, 07 Mar 2016 14:14:59 GMT

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

Re: Set Analysis Multiple Specific Dates

Anonymous — Mon, 07 Mar 2016 15:02:41 GMT

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

swuehl — Mon, 07 Mar 2016 15:10:18 GMT

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

Anonymous — Mon, 07 Mar 2016 15:32:35 GMT

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