Set Analysis: select single date, but return sum for the entire week

[jhdalben]

Hello,

I'm trying to write a set analysis expression to display the sum of a field based on the entire week of a selected date. What I have by now is the following:

Sum({$<DATE_FIELD={">=$(=WEEKSTART(Min(DATE_FIELD)))<=$(WEEKEND(Max(DATE_FIELD)))"}>} SALES)

However, when I select a single date, this sum returns the sum only for that date, and not for the entire week. Any ideias on how to implement this?

Also, if a user selects two dates from two distinct weeks, the expression should return the sales of both weeks.

Thank you for your attention.

[rwunderlich]

Add a modifier to ignore the DATE_FIELD selection:

Sum({$<DATE_FIELD={">=$(=WEEKSTART(Min(DATE_FIELD)))<=$(WEEKEND(Max(DATE_FIELD)))"}, DATE_FIELD=>} SALES)

Alternatively, if you have a Week field in your calendar you can probably simplify to:

Sum({$<Week=P(DATE_FIELD), DATE_FIELD=>} SALES)

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[jhdalben]

Rwunderlich,

Sum({$<DATE_FIELD={">=$(=WEEKSTART(Min(DATE_FIELD)))<=$(WEEKEND(Max(DATE_FIELD)))"}, DATE_FIELD=>} SALES)

This does work but makes my table display all the weeks contained in the data model,  which makes the app really slow.  Any way eliminate this behavior? If a user selects a date, the table should display only the week containing that date, with the total SALES value for that week, and not on that date, or all the weeks in the model.

Thank you.

[jhdalben · Accepted solution]

Actually, Rwunderlich's solution does work, but there was a typo just before WEEKEND, and there was no need for the DATE_FIELD= part

Sum({$<DATE_FIELD={">=$(=WEEKSTART(Min(DATE_FIELD)))<=$(=WEEKEND(Max(DATE_FIELD)))"}} SALES)

What was happening was that I had two objects do filter dates: DatePicker, which dealt directly with the DATE_FIELD field, and a simple date filter, which had a dimension Date, given by Date(DATE_FIELD, 'DD/MM/YYYY'), and filtering with these two objects had different impacts on my table object. As the expression uses the DATE_FIELD field, only the DatePicker object worked, but the simple filter didn't. 

Thanks for the help!