topic Re: Set analysis or aggr? in QlikView

Set analysis or aggr?

alisonpwallis — Wed, 12 Mar 2014 16:08:48 GMT

I have a gauge which I want to show for a subset of my data defined by selecting certain values from two particular fields. I am using set analysis for the first subset which enables me to select data where a particular condition is met but I can't work out how to select the second subset.

So: I have a target for numbers of students which is dependent on where they come from - either the EU or outside the EU. I want a gauge for each target. I want the pointer to show for that gauge (i.e. for those from the EU only) the numbers with an appropriate status (i.e. that they have applied and been accepted). So I have this initially:

=sum({$ <[Application Status]={"U/F","C/F"}>} [FTE])

where Application Statuses of U/F and C/F mean the students have accepted their offers. So I need to include somewhere in this statement the Location field which would be [Residency Desc]='EU' for the EU target (for example) otherwise I get the same value for both gauges.

Can I do this by nesting the set analysis? Or can I use aggr? Because I want to set conditions on both fields I am having trouble!

Any help would be very much appreciated!

Thanks

Alison

Re: Set analysis or aggr?

MK_QSL — Wed, 12 Mar 2014 16:11:32 GMT

You can use like ..

FOR EU

=sum({$ <[Application Status]={"U/F","C/F"},[Residency Desc]={'EU'} >} [FTE])

and

FOR NON EU

=sum({$ <[Application Status]={"U/F","C/F"},[Residency Desc]-={'EU'} >} [FTE])

Re: Set analysis or aggr?

jpapador — Wed, 12 Mar 2014 16:12:43 GMT

If I understand you correctly you also want to add a filter that only targets EU? Try this:

sum({$ <[Application Status]={"U/F","C/F"}, [Residency Desc]={'EU'}>} [FTE])

Re: Set analysis or aggr?

Wed, 12 Mar 2014 16:14:09 GMT

Please try something like...

=sum({$ <[Application Status]={'U/F','C/F'}, { [Residency Desc]={'EU'}} >} [FTE])

Re: Set analysis or aggr?

alisonpwallis — Wed, 12 Mar 2014 16:22:34 GMT

Thank you everyone - it works