topic Re: Set Analysis - All values except field X in QlikView

Set Analysis - All values except field X

Wed, 22 May 2013 05:16:02 GMT

I have an expresion that includes all the data from set. I'm using Sum( {1} Sales ). Now unfortunatelly I need to modify the expresion to get into consideration all the values from the set except one filter. Any ideas how can I achieve this? I was thinking to use set modifiers or indirect set analysis.

Re: Set Analysis - All values except field X

Wed, 22 May 2013 08:59:35 GMT

If you want to ignore the selections made against one filter, add that into your set modifier to select everything like this (lets assume you want to ignore selections against REGION):

Sum( {1<REGION={'*'}> Sales )

Or, I think this might also work:

Sum( {1<REGION=> Sales )

Regards,

Nigel.

Re: Set Analysis - All values except field X

Wed, 22 May 2013 09:16:18 GMT

try this

sum( {<field = E({1<field={"X"}>})>} Sales)

Re: Set Analysis - All values except field X

teempi — Wed, 22 May 2013 09:31:15 GMT

Hi ivozen,

It's surprisingly hard to achieve what you need but this is the way I got it working (assuming I understood your problem correctly):

1. Create a variable, for example v_set_values. Type in this expression and rename 'IncludedFilter' so that it matches your data:

= If(GetSelectedCount(IncludedFilter) = 0,

Concat({1} DISTINCT '"' & IncludedFilter & '"', ','),

'"' & GetFieldSelections(IncludedFilter, '","', 100000) & '"')

This should work with any kind of field value but I wouldn't use this method if the field has a lot if distinct values.

2. In your object you should now be able to use this expression:

= Sum({1< IncludedFilter={$(v_set_values)} >} SomeValue)

Basically what this does is it calculates the sum of field SomeValue excluding all selections in all fields except IncludedFilter. Was this what you wanted to achieve?

-Teemu

Re: Set Analysis - All values except field X

jerrydimaso — Fri, 26 Jul 2013 20:01:35 GMT

If you want to include all field selections except field X, then this is what you want:

SUM({<X=>} Sales

If you want to ignore all field selections except field X, then you want this:

1. Create a variable

vFieldX= =chr(39)&Concat(DISTINCT X, chr(39)&','&chr(39))&chr(39)

2. Use this expression

SUM({1<X={$(=vFieldX)}>} Sales)

Similar to Teemu's solution, but with a simpler variable.

Re: Set Analysis - All values except field X

Anonymous — Wed, 16 Mar 2016 06:18:09 GMT

Hello,

Maybe a simple X::$X would also work with using 1 for the set analysis.

Sum({1<X::$X>} Sales)

Regards,

Saurabh

Re: Set Analysis - All values except field X

Joris_VM — Tue, 29 Jun 2021 10:04:52 GMT

You could try Sum({1<X=P(X)>}Sales)

It should give you the sum of al your sales taking limited to all possible values of X, wether explicitely selected or not