topic Set Analysis Excluding All Selections in QlikView

Set Analysis Excluding All Selections

Thu, 30 Apr 2009 05:40:18 GMT

Hi All,

Using Set Analysis can we Exclude all the selections Except one field.

sum({1<FIELD1=FIELD1>} Sales) : This does not work.

Does any one have other alternatives.

Regards,

MAK

Set Analysis Excluding All Selections

Thu, 30 Apr 2009 10:43:51 GMT

sum( {1<FIELD={Value} Sales>} This works but it is restricted to this Value. I want it to be depended on only one field selection.

Can any body do this?

Set Analysis Excluding All Selections

yblake — Thu, 30 Apr 2009 14:23:13 GMT

to get all selection except set A, use {1-A}

Set Analysis Excluding All Selections

rbecher — Thu, 30 Apr 2009 18:47:39 GMT

I'm not sure if I understand what you want, but

sum( {1-$} Sales )

will summarize all except the selections.

Set Analysis Excluding All Selections

Thu, 30 Apr 2009 20:36:33 GMT

As you said , {1-$} will give All except the selections.

Here is what I want exactly

If I make selections on Field1 , Field2 , Field3....., Field100.

I want to summarize by Ignoring all selections but Including Field3 Selection.

Set Analysis Excluding All Selections

rbecher — Thu, 30 Apr 2009 22:07:23 GMT

This will summarize all Sales values with the current Field3 selection:

sum( {1 < Field3 = {"$(=getfieldselections ( Field3 ))"} >} Sales )

Will work only if a single value is selected.

Set Analysis Excluding All Selections

Thu, 30 Apr 2009 23:31:57 GMT

Thanks Ralf.

Is there any solution for Multiple Values?

Set Analysis Excluding All Selections

rbecher — Thu, 30 Apr 2009 23:50:36 GMT

I played around a bit and this works now for me:

sum( {1 < Field3 = {$(=getfieldselections ( Field3 ))} >} Sales )

Just without the quotes! 😉

Set Analysis Excluding All Selections

Fri, 01 May 2009 01:20:22 GMT

Thanks Ralf,
I tried removing Quotes. It doesnt work.

Did you change anything else?

Set Analysis Excluding All Selections

rbecher — Fri, 01 May 2009 03:30:51 GMT

No. It depends on the values of Field3. Do you have values with blanks or strange chars?

This is my last idea:

sum( {1 < Field3 = {$(=getfieldselections ( Field3, ',', 100 ))} >} Sales ) // getfieldselections with comma separator, max 100 values (default is 6)

You can check the return of getfieldselections ( Field3, ',', 100 ) if you insert it as a formula. It should return a comma separated value list.

Set Analysis Excluding All Selections

Fri, 01 May 2009 03:44:51 GMT

Thank You Very Much.

I had "-" in my value, that is why it was giving problem. Now solved.

Set Analysis Excluding All Selections

Tue, 05 May 2009 20:55:02 GMT

=sum( {1 <Field3= {$(= '"'& concat( Field3,'", "' )& '"')}>} Sales)

Some improvement to your code. This works even for bad data.

Set Analysis Excluding All Selections

rbecher — Tue, 05 May 2009 21:05:20 GMT

This will return a quoted value list which is much better. But I wonder why you're using concat instead of getfieldselection?

Set Analysis Excluding All Selections

Tue, 04 May 2010 22:35:19 GMT

The above solution is good for QV 8.5 and it makes the chart little slow.

Is there any other faster solution in V 9.0 or V 10

Set Analysis Excluding All Selections

johnw — Tue, 04 May 2010 23:36:14 GMT


Ralf Becher wrote:I wonder why you're using concat instead of getfieldselection?

Well, the difference would be if nothing is selected, everything ends up on the concat() list, and nothing ends up on the getfieldselections() list. So it depends on if you want nothing or everything, I guess.

I'm not on 9.0 yet, but doesn't it let you specify the set of all possible values for a field? So you can very directly just say "ignore all selections, except for this field, where you should use all possible values". Someone else will have to supply the syntax. I suppose I could look it up if I weren't lazy, but I'm lazy.

Set Analysis Excluding All Selections

tanelry — Wed, 05 May 2010 10:07:27 GMT

> =sum( {1 <Field3= {$(= '"'& concat( Field3,'", "' )& '"')}>} Sales)
> Some improvement to your code. This works even for bad data.

Just for refinement, try if this improves the performance: ... concat( distinct Field3, ...

By ignoring any duplicate values, it should work better in case your Field3 is heavily populated.

Set Analysis Excluding All Selections

johnw — Wed, 05 May 2010 16:43:33 GMT

[quote user="Tanel Rüütli"]

> =sum( {1 <Field3= {$(= '"'& concat( Field3,'", "' )& '"')}>} Sales)
> Some improvement to your code. This works even for bad data.

Just for refinement, try if this improves the performance: ... concat( distinct Field3, ...

By ignoring any duplicate values, it should work better in case your Field3 is heavily populated.

Ah, yes. I've made THAT mistake before. Definitely use distinct if your field values are not already distinct. It can make a huge difference in performance.

Set Analysis Excluding All Selections

Wed, 05 May 2010 20:13:17 GMT

This Works for V9.0

sum( {1 <Field3= P(Field3)>} Sales)