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

Set analysis - ignores filters on all fields but one

m_woolf — Thu, 30 Aug 2012 17:53:36 GMT

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.

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

MayilVahanan — Thu, 30 Aug 2012 17:58:50 GMT

Something like this,

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

Hope it helps

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

m_woolf — Thu, 30 Aug 2012 18:01:36 GMT

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.

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

whiteline — Thu, 30 Aug 2012 18:06:33 GMT

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

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

millnet-maho — Wed, 17 Jul 2013 08:38:32 GMT

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.

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

liviumac — Thu, 05 Nov 2015 11:19:58 GMT

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

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

natebrunner — Thu, 21 Jan 2016 21:36:53 GMT

Agree...looking for same thing.

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

oscaraguilar — Wed, 29 Nov 2017 17:46:20 GMT

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.

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

mongolu — Thu, 18 Jan 2018 19:58:04 GMT

Hello.

Did you found any solution to this problem?

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

mkelemen — Mon, 22 Jan 2018 12:52:21 GMT

Hi Mihai,

this might help you.

Ignore all selections except some specific fields using Set Analysis

BR,

Matus

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

trust_okoroego1 — Tue, 12 Jun 2018 16:47:25 GMT

Please be careful when using this solution. What this FieldToKeep=P(FieldToKeep) means is this:

get all possible values associated with the values in the field FieldToKeep. So if any of the filedsvalue is null, any associated record will be excluded.

for example, you have below record:

A B C

1 3 4

2 2 3

2 4

sum({ c=p(c) } B ) will give 5 instead of 9 even without a selection on C.

For this solution to work, ensure that you don't have any null value or null reference to the fact you are aggregating.

Below is a better solution though could be slow for huge datamodel: Ignore all selections except some specific fields using Set Analysis

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

emiledavis — Mon, 10 Sep 2018 15:54:52 GMT

Hi,

This is a small add-on to the solution that Matus pointed out. The article (a great article) he points to seems to use just one set. I was in a situation where I wanted to ignore selections of all fields from several tables but wanted to exclude a couple fields that are in those tables. I used the following:

=concat(

{

1<$Table = {'Table1','Table2','Table3'}> //Set containing the tables containing the fields you want to ignore selections

- //use the Exclusion set operator to NOT include the next set

<$Field={'Field_to_Exclude1','Field_to_Exclude2'}> //set containing the "Exception" fields

}

'[' & $Field & ']' ,'=,') & '=' //adding the [ and ] brackets around the field name and also adding the equals sign at the end of the last field in the created array

I use this for Date period labels for Expression Labels. So I first use the above as a vSet_Variable.

Then the label expression becomes:

maxstring({<$(vSet_Variable), Other_restrictions_as_needed_for_custom_time_period }> Month)

Enjoy!

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

maykerreyes — Wed, 05 Dec 2018 19:59:25 GMT

Thank you.

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

Hypersnooze — Wed, 27 Nov 2019 09:21:41 GMT

Hey guys,

What if I only wanted to ignore the filters on ONE field?

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

jaibau1993 — Wed, 27 Nov 2019 09:28:52 GMT

Sum({<FieldToIgnore>} Sales)

Jaime.

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

Hypersnooze — Wed, 27 Nov 2019 09:35:39 GMT

Thanks, but it doesn't seem to do it for me.

I'm using a line:

FirstSortedValue ({<StartDate>} DISTINCT StartDate, PersonID)

Perhaps the bypass only works with aggregations and not 'FirstSortedValue'?

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

jaibau1993 — Wed, 27 Nov 2019 09:42:28 GMT

Hi:

You are quite probably interchanging "FirstSortedValue" arguments. I think you may want to use

FirstSortedValue ({<StartDate>} DISTINCT PersonID,StartDate)

(firts argument: what to show. Second argument: sort criteria)

Jaime.

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

MMachado — Fri, 17 Jul 2020 12:50:54 GMT

This formula changed my life!
LOL

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

marco_puccetti — Fri, 17 Jul 2020 14:24:04 GMT

Hello everybody, perhaps this can help someone:

sum({$<Year=, Quarter, Month>}LineSalesAmount)

Isn't it?

Mark