6 Replies Latest reply on Feb 14, 2012 2:04 AM by Andre Goehler

# Calculated field immune to filter

Hello,

I'm trying to write a calculation to create Z Scores which are not affected by filters.

I have a data set which includes a list of Speakers at workshops around the world and a list of questions the workshop attendees give satisfaction scores on a scale of 1-7.

I would like to calculate the advisers Z Score, but also be able to filter by individual speaker.

Currently I have produced the Z Scores for ALL speakers, but now when I filter it down to 1 speaker the table calculations do not work correctly.

Does anyone know a way for me to calculate Z Scores that are not affected by filters?

1. I need the Total Average of all Speakers averages. This calculation should not be affected by a filter in order to calculate:

(X - Xmean) / standard Deviation

2. Also I would be satisfied with

[Xmean(filter=on) - Xmean(filter=off)] / Standard Deviation

Regards, Andre

• ###### 1. Re: Calculated field immune to filter

There is a trick which may help.

Filters on the results of table calculations are applied after all calculations have been evaluated. You can create a dummy table calculation which just returns the value of the field you want to filter on. That will limit the displayed rows to the ones selected in the filter, without affecting your calculations.

Say you want to filter on [Speaker], you would create a field called [Speaker_Filter] (say) like this:

LOOKUP( ATTR( [Speaker] ), 0)

That just looks up the value of [Speaker] in the current row.  Put that on the filters shelf and it should do what you want.

• ###### 2. Re: Calculated field immune to filter

Hi, Richard thank you for the quick response.

unfortunately it is not working. When i use it to filter it does the same thing as before.

• ###### 3. Re: Calculated field immune to filter

Are you able to put together a simple mock-up of your workbook that you can share?  I'm sure that approach can be made to do what you want - either I haven't understood how your original worksheet is structured or you haven't got the filter defined how I was trying to describe. Or maybe I'm just wrong! ;-)

• ###### 4. Re: Calculated field immune to filter

the same to you

• ###### 5. Re: Calculated field immune to filter

thank you very much

• ###### 6. Re: Calculated field immune to filter

Thanks very much Richard we could sort it out now.

We just created a Show/ Hide Calculation combined with a parameter.

Say: Parameter Displays a Speaker and if the Speaker matches the Display then show, else Hide

>>

IF [Speaker] = [Display] then 'Show' else 'Hide' END

And Display: String - Speaker