topic Re: Re: Grouping conditions in Set Analysis in QlikView

Grouping conditions in Set Analysis

chriscammers — Thu, 14 Aug 2014 16:36:19 GMT

I have some syntax that I'm not sure how to implement in set analysis.

I have a series of set conditions that I am getting the union of like this

Sum({<Thing1 = {'arg1','arg2'}>+<Thing2 = {'arg1','arg2'}>}Sales)

Now I want to add a condition that limits by time but I want it to apply to both parts of the union

So a simple example would be to do something like this...

Sum({<Year = {'2014'},Thing1 = {'arg1','arg2'}>+<Year = {'2014'},Thing2 = {'arg1','arg2'}>}Sales)

However my real expressions are WAY more complicated than this so I am wondering if there is syntax in set analysis that allows me to group these conditions similarly to the way you would use parenthesis in sql like this...

Where Year = 2014 and ( Thing1 In ('arg1','arg2') OR Thing2 = ('arg1','arg2'))

Also I have used in the past an operator like -= to exclude values from a set like this...

Sum({<Year = {'2014'}, Month -={'January'}>}Sales)

Does the union Operator work the same way in this case?

Sum({<Year = {'2014'}, Month +={'January'}>}Sales)

So would this expression give me 2014 plus any january?

Ugh, That's probably it.

I'm going to test

Thanks

Chris

Re: Grouping conditions in Set Analysis

Thu, 14 Aug 2014 17:46:26 GMT

You can maybe use a combination of If and set analysis like this:

Sum({<Thing1 = {'arg1','arg2'}>+<Thing2 = {'arg1','arg2'}>} if(Year=2014,Sales))

Also Sum({<Year = {'2014'}, Month +={'January'}>}Sales) will just give every month plus Jan for Year 2014. So if there are no selections result will be all months in 2014. Say Dec is selected then Jan 2014 and Dec 2014 results.

Attached is an example. The text box has the expression.

Thanks

Re: Re: Grouping conditions in Set Analysis

chriscammers — Fri, 15 Aug 2014 12:53:25 GMT

I'm sorry but that sounds terrible for performance. I have WAY too much data to start writing sum(If()) expressions.

However I think I did find the syntax that will work.

I found that you can use parenthesis within the set analysis expression to do some grouping. So with some parenthesis and the intersection operator I was able to do what I wanted without having to repeat the time condition.

So here is the syntax...

sum({<$(TimeSetVar)> * (1<Thing1 = {'arg1','arg2'}>+ 1<Thing2 = {'arg3','arg4'}>)}Sales)

Now for the explanation...

{

<$(TimeSetVar)> //This set uses a variable that overrides all calendar selections and sets the "master date" to a range

* //This is the intersection operator

( //Parenthesis to group the next two sets

1<Thing1 = {'arg1','arg2'}> //condition 1: notice I am starting with all the selections

// overridden this is just in case the selections are outside

// the limits of the "time set" in the first condition.

+ //Union operator serves as the "OR" between the two sets

1<Thing2 = {'arg3','arg4'}> //Condition 2

) //Closing Parenthesis closes the group of conditions

}

I hope that makes sense, I'm going to figure out how to explain it better and blog about it.

I'm attaching a sample that has a working but unfortunately kinda dumb example, the data is from the Wisconsis State trap meet, my kid is on the waterford team and I was playing with the data.