topic Re: Set Analysis help needed in QlikView

Set Analysis help needed

linoyel — Thu, 06 Mar 2014 09:55:03 GMT

Hey All,

I need help with a Set Analysis.

I have a certain calculation as an expression in a table:

Count (DISTINCT if(cctranzresponse=000 AND Table_No=1,unique_num,NULL())) / Count(DISTINCT if(Table_No=1,unique_num,Null()))

I need in to be for a certain Bookmark selection "AppRate" and also for a certain country (BirthCountryKey = 161).

I wrote this - but it doesn't work:

Count ({AppRate + {<BirthCountryKey = {161}>} DISTINCT if(cctranzresponse=000 AND Table_No=1,unique_num,NULL())) / Count({AppRate + {<BirthCountryKey = {161}>} DISTINCT if(Table_No=1,unique_num,Null()))

Any suggestions please?

Re: Set Analysis help needed

Thu, 06 Mar 2014 10:00:50 GMT

Hi Linoy

Check this post from Stefan

Re: looking for the correct syntax : bookmarks + set analysis

Using it based on your above expression I would write this

Count( {<BirthCountryKey {161}>+AppRate } DISTINCT if(cctranzresponse=000 ANDTable_No=1,unique_num,NULL())) / Count({<BirthCountryKey = {161}>+AppRate} DISTINCTif(Table_No=1,unique_num,Null()))

Re: Set Analysis help needed

linoyel — Thu, 06 Mar 2014 10:51:29 GMT

Hi Byron,

Anyway it's supposed to be <BirthCountryKey = {161}>, and not <BirthCountryKey {161}>...

My code I wrote above presents the result based just on the bookmark but it ignores the country.

I need it to be bookmark (it's like a SELECT statement) AND ALSO country (its' like WHERE country = ...)

The bookmark replaces a few "current selections", and country is like an additional "current selection".

Re: Set Analysis help needed

jonathandienst — Thu, 06 Mar 2014 11:10:20 GMT

I think you just have an extra opening curly bracket in your expression. Try this:

Count ({AppRate + $<BirthCountryKey = {161}>} DISTINCT if(cctranzresponse=000 AND Table_No=1,unique_num,NULL()))

/ Count({AppRate + $<BirthCountryKey = {161}>} DISTINCT if(Table_No=1,unique_num,Null()))

HTH

Jonathan

Edit: you can change the $ to 1 if you want to ignore selections in the second part of the set expression

Re: Set Analysis help needed

Thu, 06 Mar 2014 11:11:55 GMT

Well done on pointing out my copy paste mistake. Will be extra vigilant next time

Help File:

Several set operators that can be used in set expressions exist. All set operators use sets as operands, as described above, and return a set as result.

+ Union. This binary operation returns a set consisting of the records that belong to any of the two set operands.

- Exclusion. This binary operation returns a set of the records that belong to the first but not the other of the two set operands. Also, when used as a unary operator, it returns the complement set.

* Intersection. This binary operation returns a set consisting of the records that belong to both of the two set operands.

Based on this, and the fact you don't want it to belong to any two set operands but actually both, country set statement and bookmark, then use the intersection *

Re: Set Analysis help needed

linoyel — Thu, 06 Mar 2014 11:46:08 GMT

Well, this expression works:

Count ({AppRate * 1<BirthCountryKey = {161}>} DISTINCT if(cctranzresponse=000 AND Table_No=1,unique_num,NULL())) / Count({AppRate * 1<BirthCountryKey = {161}>} DISTINCT if(Table_No=1,unique_num,Null()))

Thank you very much guys!