Simple way to exclude all fields (100+) from set analysis except specified fields?

[#1]

So I've been scouring the forums and have yet to see a simple answer to this question. If it exists, apologies!

Say I have a dataset with hundreds of fields.

I have loaded estimates via concatenation with only 4 of the corresponding fields....but the user may filter on any of the xxx-4 fields.

I want the estimates to always be displayed...and only change when a user filters on a field that corresponds to the estimates data.

Instead of writing a set like this:

=num(sum({$<[PL] = {'Finance'}, Fiscal_Quarter=, PROJECT_COST_CENTER =, DR_COST_CENTER=, PROJECT_NAME =, ETC...for every single field I want to ignore>} [Revenue Projection]),'##.#' )

....Is it possible to have my set only take the four fields that I specify? Rather than writing out the 90+ that I want to ignore?

[swuehl]

Maybe like this?

Ignore all selections except some specific fields using Set Analysis

[sunny_talwar]

May be something like this:

Sum({1<DontIgnoreField1 = $::DontIgnoreField1, DontIgnoreField2 = $::DontIgnoreField2, DontIgnoreField3 = $::DontIgnoreField3, DontIgnoreField4 = $::DontIgnoreField4>}[Revenue Projection])

or this

Sum({1<DontIgnoreField1 = p(DontIgnoreField1), DontIgnoreField2 = p(DontIgnoreField2), DontIgnoreField3 = p(DontIgnoreField3), DontIgnoreField4 = p(DontIgnoreField4)>}[Revenue Projection])

[#4]

Hi Stefan, thank you for your response!

I may have misinterpreted that post when I was looking at it earlier...but I thought it was referring to excluding certain selections within a field, not the fields themselves. Please help me understand your reasoning...or where I misinterpreted the post/can apply the same logic to excluding fields. Appreciate it

[Anonymous]

Probably not a 'simple' way.  You could see if this meets your need:  https://community.qlikview.com/thread/60630.

Otherwise, in your script you could create a variable listing all the field names you do NOT want to be filterable. 

Something like:

Let  vExcludeFIelds='Fiscal_Quarter=, PROJECT_COST_CENTER =';

But you could have the script loop through all the fields so that it builds the variable for you with all the fields you want to not be filterable.

Then in your expression you would only need to reference the variable. Something like:

=num(sum({$<[PL] = {'Finance'}, $(vExcludeFields)>} [Revenue Projection]),'##.#' )

[#6]

Hi Sunny,

Thank you for the expressions.

Tried both of these but they did not appear to work.

[sunny_talwar]

When you mention they don't work, can you elaborate what is the issue you are running into? Would you be able to share a sample for us to help you better?

[swuehl · Accepted solution]

Brain, the dollar sign expansion presented at the bottom of that post should basically expand to a complete list of the fields you want to clear (your 90+ field names).

Just add all field names you don't want to clear (i,e, selections should still be considered) to the element list here:

...Field-={'Stock','Manager'}...

[#9]

Hey Wallo,

Great thinking! Thanks for the help!

[swuehl]

Sunny, p() function will still be influenced by selections in other field.

$::FIELD seems not to work (at least in my version), if you expect to copy the default state selection of field FIELD.

Might work if you use an additional alternate state and use that in the aggregation set identifier, though.