2 Replies Latest reply on Apr 23, 2018 7:31 AM by Stephen Groff

    Cannot Mix Aggregate & Non-Aggregate comparisons - help

    Stephen Groff

      I have a valid calc that tells me how many machines I have built:  MACHINES (measure)

       

      COUNTD(

          IF ([Product Family]='ACOM' OR [Product Family]='SCOM' OR [Product Family]='LAP' OR [Product Family]='RM' OR [Product Family]='SCRD')

              AND ([FA ID]='400' OR [FA ID]='450')

              AND [Prototype]='N'

              AND [EXT_CPY_STAT]<>'D'

              AND [Serial Prefix]<>'BOE'

              THEN [Serial #]

              ELSE NULL

          END

      )

       

      I have a valid calc that tells me how many defects on the assembly line there have been: DEFECTS (measure)

       

      COUNT(

      IF ISNULL([Disposition])

              AND [Prototype]='N'

              AND [EXT_CPY_STAT]<>'D'

              AND ([Product Family]='ACOM' OR [Product Family]='SCOM' OR [Product Family]='LAP' OR [Product Family]='RM' OR [Product Family]='SCRD')

              AND [CA ID]<>'0_DEFECTS'

              AND NOT STARTSWITH([Event Desc],'0') AND NOT CONTAINS([Event Desc],'defects')

              AND [FA ID]<>'501' AND [FA ID]<>'525' AND [FA ID]<>'600' AND [FA ID]<>'700' AND [FA ID]<>'701' AND [FA ID]<>'702' AND [FA ID]<>'703' AND [FA ID]<>'704' AND [FA ID]<>'705' AND [FA ID]<>'706' AND [FA ID]<>'800' AND [FA ID]<>'900'

              AND [Q/V]='Q'

          THEN [Event #]

          ELSE NULL

      END

      )

       

      Using this information, I need to find out which machines did not have a defect in a certain quality check ([FA ID] '400' & '450') but I have no idea how to do this.

       

      In Excel I would simply =Calculate(Countrows(Defects),Defects[Found Area ID]="400", Defects[Found Area ID]="450")

       

      How do I do this in Tableau?

       

      My current thinking is telling me to do an IF statement, but I'm just guessing....

       

      Count(

           IF [FA ID]='400' OR [FA ID]='450'

           THEN [Machines]

           ELSE NULL

      END

      )

       

        I would attach the file, but Corporate IT Security prevents me from giving you guys anything to look at.  Thanks all

        • 1. Re: Cannot Mix Aggregate & Non-Aggregate comparisons - help
          Zhouyi Zhang

          Hi, Stephen

           

          You can use your logic for defect but without count() as flag for defect

          IF ISNULL([Disposition])

                  AND [Prototype]='N'

                  AND [EXT_CPY_STAT]<>'D'

                  AND ([Product Family]='ACOM' OR [Product Family]='SCOM' OR [Product Family]='LAP' OR [Product Family]='RM' OR [Product Family]='SCRD')

                  AND [CA ID]<>'0_DEFECTS'

                  AND NOT STARTSWITH([Event Desc],'0') AND NOT CONTAINS([Event Desc],'defects')

                  AND [FA ID]<>'501' AND [FA ID]<>'525' AND [FA ID]<>'600' AND [FA ID]<>'700' AND [FA ID]<>'701' AND [FA ID]<>'702' AND [FA ID]<>'703' AND [FA ID]<>'704' AND [FA ID]<>'705' AND [FA ID]<>'706' AND [FA ID]<>'800' AND [FA ID]<>'900'

                  AND [Q/V]='Q'

              THEN [Event #]

              ELSE NULL

          END

           

          and then use your logic for machine to flag the machine as well

              IF ([Product Family]='ACOM' OR [Product Family]='SCOM' OR [Product Family]='LAP' OR [Product Family]='RM' OR [Product Family]='SCRD')

                  AND ([FA ID]='400' OR [FA ID]='450')

                  AND [Prototype]='N'

                  AND [EXT_CPY_STAT]<>'D'

                  AND [Serial Prefix]<>'BOE'

                  THEN [Serial #]

                  ELSE NULL

              END

           

           

          And then you can create something like

           

          If Not  isnull([machine flag]) and isnull(defect flag) then [machine flag] end

           

           

          ZZ

          1 of 1 people found this helpful
          • 2. Re: Cannot Mix Aggregate & Non-Aggregate comparisons - help
            Stephen Groff

            ZZ - Thanks for the reply.   I'm trying to understand the purpose of your solution, I was hoping you could spell it out for me...

             

            I believe I got rid of the COUNT on both the Machines and Defects calcs as to avoid the "cannot mix aggregate and non-aggregate comparisons" error.  My results were the exact same as they would've been if I had left the 'COUNT' in the function.  That I can understand.

             

            But I'm still unsure I understand the following solution:

            If Not  isnull([machine flag]) and isnull(defect flag) then [machine flag] end

             

            I understand your calculation as:

            iF MACHINE ISN'T NULL BUT THE DEFECT IS, THEN COUNT IT. ELSE IGNORE IT

             

            What I'm trying to determine in my head:

            USE [machine flag] to search in [defect flag]

            IF it finds [machine flag] in [defect flag] and within [defect flag] that defect was found in [fa id]='400' OR '450'

            then DEFECT found at Quality Area

            else NO DEFECT FOUND

            END

             

            Ultimately I am trying to find the # of defect free machines in [FA ID}='400' OR '450

            1 of 1 people found this helpful