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.

    Andrew Radil

      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.