Set analysis with two conditions

[alexbjorlig]

Hello Qlik Community. I am very new to Qlik, so I don't hope this question is already answered. I searched a lot, but cannot get it to work.

My sheet contains a "date picker" and a "Team picker". My objective is to present the forecast for next week, based on the users selection. I.e. if the user selects week 15, it should show the forecast for week 16. This part works.

The problem is that the forecast is presented for all teams, and not limited by the selected teams.

sum({1<Calendar_Date={">=$(=weekstart(Date(Max(Calendar_Date)+7)))<=$(=Date(weekstart(Date(Max(Calendar_Date)+7))+6))"}>}M_ForecastHours)

I can get it to work by doing this:

=sum({1<Calendar_Date={">=$(=weekstart(Date(Max(Calendar_Date)+7)))<=$(=Date(weekstart(Date(Max(Calendar_Date)+7))+6))"}>}{$<Employee_TeamName={"Team ABC"}>}M_ForecastHours)

But that is not very dynamic

I really hope you can help me out.

[#2]

Hi Alex,

sum({1

The 1 in your set analysis is telling QV to ignore all user selection and calculate on the whole data set. Change this to

sum({$

if you want user selections of team etc to be regarded in the result

hope that helps

Joe

[alexbjorlig]

Hi JoeSimmons.

Thank you for taking your time to answer. Unfortunately if I change $ to 1 in the date part of the expression, I only get the dates the user selected. Let me explain it like this:

1) The user selects week 37

2) I wan't to present him with the forecast for next week. But that is not possible if I change $ to 1, because that is not part of the users selection...

Any ideas to solve this?

[maxgro]

if the week field is Week

=

sum({<Week=,Calendar_Date={">=$(=WeekStart(max(Calendar_Date)+7))<=$(=WeekEnd(max(Calendar_Date)+7))"}>} M_ForecastHours) 

[alexbjorlig]

Hi Maxgro.

I have a dimension called Calendar_Week, and when I use your expression I get the right dates, but no measures

=sum({<Calendar_Week=,Calendar_Date={">=$(=WeekStart(max(Calendar_Date)+7))<=$(=WeekEnd(max(Calendar_Date)+7))"}>} M_ForecastHours)

[alexbjorlig · Accepted solution]

I finally solved the problem the following way:

I created a variable vTestTeamName with the following definition: =EmployeeTeamName

Then I modified the Set Analysis to this:

=sum({1<Calendar_Date={">=$(=weekstart(Date(Max(Calendar_Date)+7)))<=$(=Date(weekstart(Date(Max(Calendar_Date)+7))+6))"}, Employee_TeamName={"$(vTestTeamName)"}>}M_ForecastHours)

And now it works!

Thanks for the help and inspiration.

[alexbjorlig]

I created a small application to demonstrate the problem.

https://www.dropbox.com/s/ybhvh37tq2epv37/Question.qvw?dl=0

As you can see in this qlikview file, everything works as intended besides the Employee Name filter for the previous week column.

Brgds, Alex