Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

mwoolf
Honored Contributor II

Set analysis - ignores filters on all fields but one

Can set analysis ignore filtering on all but a single field?

I know that:

     sum({1}Fieldname)

sums all values in Fieldname, ignoring any filtering.

I want a similar expression that will ignore filters on all fields except FilterFieldname.

1 Solution

Accepted Solutions
whiteline
Honored Contributor II

Re: Set analysis - ignores filters on all fields but one

sum({1<FieldToKeep=P(FieldToKeep)>}Fieldname)

View solution in original post

16 Replies

Re: Set analysis - ignores filters on all fields but one

Hi

     Something like this,

     = sum({<month =, year=, day=>}fieldname) its exclude the month,year,day fields alone.

Hope it helps

mwoolf
Honored Contributor II

Re: Set analysis - ignores filters on all fields but one

Yes, but my application has many fields. Is there a way to exclude all fields except a single field?

It is not feasable to list every field but one in the expression.

whiteline
Honored Contributor II

Re: Set analysis - ignores filters on all fields but one

sum({1<FieldToKeep=P(FieldToKeep)>}Fieldname)

View solution in original post

Partner
Partner

Re: Set analysis - ignores filters on all fields but one

This works when you've made selections in FieldToKeep, but not in the more general case. Say you have

sum({1<Field1=P(Field1), Field2=P(Field2), ..., FieldN=P(FieldN)>} Fieldname)

because you want selections in any of those fields to affect the set of values in the sum, but no others. But selections in other fields may affect the set of possible selections in Field1 ... FieldN, which means that they still affect the sum too, indirectly. For example, Field1 might be Month and Field2 might be Weekday; if you select a SalesUnit that never had a sale on a Sunday, then Sunday will be excluded.

liviumac
New Contributor III

Re: Set analysis - ignores filters on all fields but one

hello

so has anyone solved this problem?

P function works most of the cases, but as outlined by Magnus, it does not work in all and if you are using it without being aware of its limitations, it will provide misleading results.

for me it would be great if i could ignore all selections but one or two in a sheet - this is very useful when you want to calculate the share of a selected dimension in the "total population", but the "total population" is not the total population in your database, but the total population as of a certain date, for example

so I want to be able to calculate a measure for the total population as of a certain date, regardless of what selections I make in a certain sheet

of course, I have many selectable dimensions, so it is unpractical to use a set modifier because I have to list in it each and every selectable dimension

thank you

LM

natebrunner
New Contributor III

Re: Set analysis - ignores filters on all fields but one

Agree...looking for same thing.

Highlighted
oscaraguilar
New Contributor

Re: Set analysis - ignores filters on all fields but one

It works too, just in case you want to exclude many fields, not all but a single field, for me, your idea work to my solve my issue.

mongolu
Contributor

Re: Set analysis - ignores filters on all fields but one

Hello.

Did you found any solution to this problem?

mkelemen
Contributor III

Re: Set analysis - ignores filters on all fields but one