topic Re: Using the intersection set operator in a chart expression in QlikView

Using the intersection set operator in a chart expression

Thu, 14 Jun 2012 10:54:03 GMT

Hello,

I am having trouble getting the intersection set operator to work in an expression for a bar chart. I want to count the people who belong to both set operands but the expression is returning no data. In the raw data there are people who belong to both sets. The user needs to select the term of entry for the chart conditions to be fulfilled.

Advice would be appreciated.

Here is the expression I'm currently using;

count

({$<TermofEntry = {$(= only (TermofEntry))},

LevelCode = {'PGR'},

ExtractType = {1},

YearofProgram = {'1'},

RegStatusCode = {'RE','RS'},

InstanceTerm = {$(= only (TermofEntry))},

CollegeCode = {'AF', 'SF', 'HF'},

ResearchDoctMastInd = {R}, ModeofStudy = {'FT'}

> *

<TermofEntry = {$(= only (TermofEntry))},

LevelCode = {'PGR'},

ExtractType = {1},

YearofProgram = {'2'},

RegStatusCode = {'RE', 'RS'},

InstanceTerm = {$(= only (TermofEntry)+ 100)},

CollegeCode = {'AF', 'SF', 'HF'},

ResearchDoctMastInd = {'R'}, ModeofStudy = {'FT'}

>} pidm)

Re: Using the intersection set operator in a chart expression

swuehl — Thu, 14 Jun 2012 13:27:07 GMT

I wasn't able to use the intersection operator between set modifiers in the past, too (only union and exclusion seem to work). But you can achieve something similar probably using

=count({<PersonKey =p( {<Year={2011}>})*p({<Year= {2012}>})>} distinct PersonKey)

count

({$

<pidm = p( {

<TermofEntry = {$(= only (TermofEntry))},

LevelCode = {'PGR'},

ExtractType = {1},

YearofProgram = {'1'},

RegStatusCode = {'RE','RS'},

InstanceTerm = {$(= only (TermofEntry))},

CollegeCode = {'AF', 'SF', 'HF'},

ResearchDoctMastInd = {R}, ModeofStudy = {'FT'}

> } )* p({

<TermofEntry = {$(= only (TermofEntry))},

LevelCode = {'PGR'},

ExtractType = {1},

YearofProgram = {'2'},

RegStatusCode = {'RE', 'RS'},

InstanceTerm = {$(= only (TermofEntry)+ 100)},

CollegeCode = {'AF', 'SF', 'HF'},

ResearchDoctMastInd = {'R'}, ModeofStudy = {'FT'}

>} )

pidm)

Hope this helps and that I haven't missed a bracket here (I hope the idea is clear?)

Regards,

Stefan

Re: Using the intersection set operator in a chart expression

Fri, 15 Jun 2012 13:18:16 GMT

Hi,

Thanks for this - the expression builder reads 'expression ok' but no data is being extracted. I can get the original expression working with a union operator but it's not outputting the correct data. Has anything else worked for you when using intersections?

Re: Using the intersection set operator in a chart expression

swuehl — Fri, 15 Jun 2012 13:50:26 GMT

Could you maybe upload a small sample file? Upload is available here using the advanced editor (follow link in editor).

Re: Using the intersection set operator in a chart expression

Tue, 19 Jun 2012 08:50:08 GMT

Hello,

I've attached a small sample file. I have created similar charts using the intersection operator in one and the union operator in the other for the 'Progressed to year 2' expression but as you can see no data is displayed from the intersection expression.

Thanks,

Danielle

Re: Using the intersection set operator in a chart expression

swuehl — Tue, 19 Jun 2012 09:03:16 GMT

Yes,

that's what I said, I don't think the intersection operator works correctly between two set modifier. But have you tried using the intersection operator like suggested above, within one field element set expression?

Please see attached.

Stefan

Re: Using the intersection set operator in a chart expression

Tue, 19 Jun 2012 10:35:12 GMT

Hi Stefan,

That's great - I've tried this in my actual model now and the intersection works. Thanks so much for your help with this.

Danielle