1 Reply Latest reply on Jan 11, 2019 12:11 PM by Joe Oppelt

    In calculation detect if all filters are all selected

    stephen conard
    stephen conard Jan 11, 2019 11:12 AM

      I need to create a calculated field to set a value for a reference line. In the calculated field I need to check if all filters have all selections made within each.

       

      I tried to create this with the following:

      IF (TOTAL( COUNTD( [Lot Category] ) ) = SUM( { FIXED : COUNTD( [Lot Category]  ) } ) AND

      TOTAL( COUNTD( [Mfg Area] ) ) = SUM( { FIXED : COUNTD( [Mfg Area]  ) } ) AND

      TOTAL( COUNTD( [Plan] ) ) = SUM( { FIXED : COUNTD( [Plan]  ) } ) AND

      TOTAL( COUNTD( [Product Name] ) ) = SUM( { FIXED : COUNTD( [Product Name]  ) } ) AND

      TOTAL( COUNTD( [Program Name] ) ) = SUM( { FIXED : COUNTD( [Program Name]  ) } ) AND

      TOTAL( COUNTD( [Tech type] ) ) = SUM( { FIXED : COUNTD( [Tech type]  ) } ))

      THEN SUM(INT([Track Out Goal Fab]) * 5)

      ELSE NULL

      END

       

      But this did not work.

       

      Thank you

      • 53 Views
      • Categories: Actions & Filters
      • Tags:
        • 1. Re: In calculation detect if all filters are all selected
          Joe Oppelt
          Joe Oppelt Ambassador Jan 11, 2019 12:11 PM (in response to stephen conard)

          I make two calcs (per dimension).

           

          You have the FIXED concept right.

          { FIXED : COUNTD([dimension name]) } // gives you count in the data source.

           

          I test what is on the sheet like this:

           

          { EXCLUDE [dimension1] (, [dimension2], [dimension-N]) : COUNTD([dimension name]) }

           

          ... where the list of dimensions includes all the dimensions that are used on the sheet.

           

          Yes, you can nest them all into one giant calc.  I like to compartmentalize them into separate calcs.  That's just my preferred method.