Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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.

5 Replies
Not applicable

Re: Set Analysis - All values except field X

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

Re: Set Analysis - All values except field X

try this

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

teempi
Contributor II

Re: Set Analysis - All values except field X

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
New Contributor II

Re: Set Analysis - All values except field X

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
Contributor

Re: Set Analysis - All values except field X

Hello,

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

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

Regards,

Saurabh

Community Browser