Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis - All values except field X

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.

6 Replies
Not applicable
Author

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.

Not applicable
Author

try this

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

teempi
Partner
Partner

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

jerrydimaso
Partner
Partner

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.

saurabh_pandit
Creator
Creator

Hello,

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

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

Regards,

Saurabh

Joris_VM
Contributor
Contributor

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