topic Re: set analysis with two variables for range in QlikView

set analysis with two variables for range

chrismtb — Sat, 16 Nov 2024 19:14:14 GMT

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?

Re: set analysis with two variables for range

m_woolf — Fri, 24 Jan 2020 14:59:48 GMT

https://community.qlik.com/t5/QlikView-Creating-Analytics/Set-Analysis-Date-Range/m-p/14302#M74

Re: set analysis with two variables for range

chrismtb — Fri, 24 Jan 2020 15:12:40 GMT

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.

Re: set analysis with two variables for range

m_woolf — Fri, 24 Jan 2020 15:30:46 GMT

See the attached qvw.

Re: set analysis with two variables for range

Brett_Bleess — Thu, 20 Feb 2020 18:48:02 GMT

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