1 Reply Latest reply on Jun 2, 2016 9:51 AM by Joe Oppelt

# How to create a calculated field with a filter applied within the calculation.

I have created a 2 calculated fields. One calculates surveys scores by each contractor.

sum(iif([Q3Result]=1,1,0)

+iif([Q4Result]=1,1,0)

+iif([Q5Result]=1,1,0)

+iif([Q6Result]=1,1,0)

+iif([Q7Result]=1,1,0))

/

(count([Q3Result])+count([Q4Result])+count([Q5Result])+count([Q6Result]) +count([Q7Result]))

The other is a calculated field to filter so I only see results for orders that have been surveyed by contractor.

iif([Q3Result]>0,[Q3Result],null)

+iif([Q4Result]>0,[Q4Result],null)

+iif([Q5Result]>0,[Q5Result],null)

+iif([Q6Result]>0,[Q6Result],null)

+iif([Q7Result]>0,[Q7Result],null)

This all works good. The problem: I want to put these results in a column on the on the same sheet as the total orders by contractor.. When I add the surveyed filter it affects the total orders surveyed. Is there a way to combine this filter calc with the survey calculation? Right now I have a separate sheet but the customer wants it all on one sheet and abel to be sorted together.

• ###### 1. Re: How to create a calculated field with a filter applied within the calculation.

I'm not clear on what you're asking.  Are you saying that your calcs are dependent on something being selected in a filter, and you want them to do stuff on a sheet, but you don't want the filter being applied to the sheet?

If that's it, consider using a parameter to control the calc (assuming your expectation is that a single value is getting selected in that filter -- parameters are always only single-value entities.)