Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
jhdalben
Contributor II
Contributor II

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

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.

Labels (2)
1 Solution

Accepted Solutions
jhdalben
Contributor II
Contributor II
Author

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!

View solution in original post

3 Replies
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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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!

View solution in original post