topic Re: Set analysis with two conditions in QlikView

Set analysis with two conditions

alexbjorlig — Tue, 29 Sep 2015 11:58:42 GMT

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.

Re: Set analysis with two conditions

Tue, 29 Sep 2015 15:24:08 GMT

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

Re: Set analysis with two conditions

alexbjorlig — Tue, 29 Sep 2015 19:09:07 GMT

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?

Re: Set analysis with two conditions

maxgro — Tue, 29 Sep 2015 19:36:22 GMT

if the week field is Week

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

Re: Set analysis with two conditions

alexbjorlig — Wed, 30 Sep 2015 08:21:32 GMT

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)

Re: Set analysis with two conditions

alexbjorlig — Wed, 30 Sep 2015 12:13:28 GMT

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.

Re: Set analysis with two conditions

alexbjorlig — Thu, 01 Oct 2015 08:16:06 GMT

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