Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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_ID | Name | Week_ID | Timesheet_Status | Hours_Per_Week |
1 | Steve | 7 | Outstanding | 35 |
1 | Steve | 8 | Outstanding | 35 |
1 | Steve | 9 | Outstanding | 35 |
2 | Mike | 7 | On Time | 40 |
2 | Mike | 8 | On Time | 40 |
2 | Mike | 9 | Outstanding | 40 |
3 | Jake | 7 | Outstanding | 35 |
3 | Jake | 8 | On Time | 35 |
3 | Jake | 9 | Outstanding | 35 |
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?
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.
See the attached qvw.
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