Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chrismtb
Creator
Creator

set analysis with two variables for range

Morning,

I have a problem with a set analysis that i am trying to use and it is driving me mad.

I have several connected source tables that come together to provide something like the following information:

Staff_IDNameWeek_IDTimesheet_StatusHours_Per_Week
1Steve7Outstanding 35
1Steve8Outstanding 35
1Steve9Outstanding35
2Mike7On Time40
2Mike8On Time40
2Mike9Outstanding40
3Jake7Outstanding35
3Jake8On Time35
3Jake9Outstanding35

 

I have also got on the sheet input boxes for two variables - vMinWeek and vMaxWeek

I am building a table that provides a single line for each staff member and want set analysis to provide a sum of the Hours_Per_Week where the Timesheet_Status is "Outstanding" and the Week_ID is between vMinWeek and vMaxWeek values.

my set analysis currently looks like this:

=aggr(sum({$<[Week_ID]={">=$(vMinWeek)<=$(vMaxWeek)"},Timesheet_Status={"Outstanding"}>}Hours_Per_Week),Staff_ID)

I have tried with single quotes and double quotes, i have tried with and without the aggregation, I have even tried splitting out the Week_ID check into two separate conditions but everything seems to keep coming back with the same numbers... any ideas?

 

 

Labels (2)
4 Replies
chrismtb
Creator
Creator
Author

Thanks Mwoolf,

I have tried the two solutions in the attached link however neither has made a difference to my calculation which still comes out with the incorrect answer.

I did note from the original thread that the user who posted originally couldn't make use of the suggested solution either.

m_woolf
Master II
Master II

See the attached qvw.

Brett_Bleess
Former Employee
Former Employee

Chris, not sure if Michael's last post got you what you needed with the example QVW or not, but I wanted to pass along a link to the Design Blog area that may prove useful on this one too:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

If you want to search this area further on your own, use the following link for that, lots of how-to posts in this area from our internal experts.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

If Michael's last post did help, do not forget to give him credit and let other Members know by using the Accept as Solution button on his post.  

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.