Set analysis - ignores filters on all fields but one

[m_woolf]

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.

[trust_okoroego1]

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

[emiledavis]

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!

[maykerreyes]

Thank you.

[Hypersnooze]

Hey guys,

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

[jaibau1993]

Sum({<FieldToIgnore>} Sales)

Jaime.

[Hypersnooze]

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'?

[jaibau1993]

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.

[MMachado]

This formula changed my life!
LOL

[marco_puccetti]

Hello everybody, perhaps this can help someone:

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

Isn't it?

Mark