8 Replies Latest reply on Sep 20, 2018 11:03 AM by Jennifer Hysuick

    Cannot mix aggregate and non aggregate in if expressions

    Jennifer Hysuick

      I have a question, and it is frustrating the heck out of all of us in the office.  I get the error up there, but what I don't get is why an aggregate IF statement cannot return anything except an aggregate result - like a string...

       

      Here is what I am trying to do:

       

      IF COUNTD([School]) =  MAX({FIXED :COUNTD([School])})

      THEN

      (

      IF (CONTAINS([Credential],"ACERT") OR CONTAINS([Credential],"APCERT") OR CONTAINS([Credential],"CERT")) THEN "Certificate"

      ELSEIF (CONTAINS([Credential],"DIP") OR CONTAINS([Credential],"DIPC") OR CONTAINS([Credential],"ADIP")) THEN "Diploma"

      ELSEIF (CONTAINS([Credential],"BACH") OR CONTAINS([Credential],"BSCN")) THEN "Degree"

      ELSEIF (CONTAINS([Credential],"APPR1") OR CONTAINS([Credential],"APPR2") OR CONTAINS([Credential],"APPR3")

          OR CONTAINS([Credential],"APPR4") OR CONTAINS([Credential],"APPUG")) THEN "Apprenticeship"

      ELSEIF (CONTAINS([Credential],"MAST") OR CONTAINS([Credential],"PGCERT")) THEN "Post Grad"

      ELSEIF [Credential] = "0" THEN "Non-Credentialed Programs"

      ELSE "Adult Education"

      END

      )

      ELSE

        [Degree Desc]

      END

       

      This is basically trying to determine if the School filter is set to All - because if it is, then I want to group certain credentials together, and if it's not (meaning one of the schools is selected) then I want to return the Degree Desc (there may be more than one) associated with the school.

       

      I do NOT want to use parameters at this time because I need a secondary filter to show only the items that are related to the school filter and parameters do not easily allow me to "nest" my filters.

       

      Can any one help fix this? I have tried multiple things to make this work.

        • 1. Re: Cannot mix aggregate and non aggregate in if expressions
          Jim Dehner

          Hi Jennifer

          the message you are getting is s simple syntax issue - you have some dimensions/measures that are aggregated (e.g. countd(school) and others[credential] that are not a

           

          you need to aggregate the ones not aggregated with something like attr() - or max() or min(0 where appropriate

           

          once the syntax is correct then you can see if the expression returns the correct value

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Cannot mix aggregate and non aggregate in if expressions
            John Hobby

            If you can, it would help to have a sample book uploaded so we can take a gander at it.  Try taking the MAX away from the FIXED ... I believe the FIXED is returning the unique count of school, so it looks like MAX is just returning that number

            • 3. Re: Cannot mix aggregate and non aggregate in if expressions
              Jennifer Hysuick

              I have tried to add an aggregate to the values I'm trying to return, but then it returns * rather than the actual values I want returned.

               

              IF COUNTD([Siast Current College Desc]) =  MAX({FIXED :COUNTD([Siast Current College Desc])})

              THEN

              ATTR(

              IF (CONTAINS([Credential],"ACERT") OR CONTAINS([Credential],"APCERT") OR CONTAINS([Credential],"CERT")) THEN "Certificate"

              ELSEIF (CONTAINS([Credential],"DIP") OR CONTAINS([Credential],"DIPC") OR CONTAINS([Credential],"ADIP")) THEN "Diploma"

              ELSEIF (CONTAINS([Credential],"BACH") OR CONTAINS([Credential],"BSCN")) THEN "Degree"

              ELSEIF (CONTAINS([Credential],"APPR1") OR CONTAINS([Credential],"APPR2") OR CONTAINS([Credential],"APPR3")

                  OR CONTAINS([Credential],"APPR4") OR CONTAINS([Credential],"APPUG")) THEN "Apprenticeship"

              ELSEIF (CONTAINS([Credential],"MAST") OR CONTAINS([Credential],"PGCERT")) THEN "Post Grad"

              ELSEIF [Credential] = "0" THEN "Non-Credentialed Programs"

              ELSE "Adult Education"

              END

              )

              ELSE

                ATTR([Degree Desc])

              END

               

               

              Using MAX rather than ATTR then returns only the highest result (alphabetically) rather than ALL the results (which is "Post Grad").

              • 4. Re: Cannot mix aggregate and non aggregate in if expressions
                Michael Hesser

                Jennifer:

                 

                This might work for you-- just a couple of edits:

                 

                IF WINDOW_COUNT(COUNTD([School]))  =  ATTR(({FIXED :COUNTD([School])})) THEN...

                //the rest of your code here

                 

                This Solution seemed to work for me, although it was messy. 

                 

                Good luck! --Michael

                • 5. Re: Cannot mix aggregate and non aggregate in if expressions
                  Jennifer Hysuick

                  Thanks for the help, but still getting the same error.

                  • 6. Re: Cannot mix aggregate and non aggregate in if expressions
                    Michael Hesser

                    Please check my edited text-- you're not including a MAX are you?

                     

                    Here's the process approach I used:

                    #1 Can I calculate the maximum number of choices? I was, using {fixed:countd([Category])}

                    #2 Can I calculate (roughly) the number of selections? I was, using WINDOW_COUNT(countd([Category]))

                    #3 Am I able to drag them onto the screen and look at them side-by-side? Sure, that's easy

                    #4 Am I able to compare them in a calc? That proved more difficult. When I tried comparing them against one another, I got our old friend, "can't mix aggregates with non-aggregates."  But once I took the attribute (ATTR) of {fixed:countd([Category])}, it worked (at least for me).

                     

                    Maybe a step-by-step approach will help you find where you need to tweak?

                    • 7. Re: Cannot mix aggregate and non aggregate in if expressions
                      Jennifer Hysuick

                      This is what I have - it is still returning * instead of the multiple word results.

                       

                       

                      IF WINDOW_COUNT(COUNTD([Siast Current College Desc])) =  ATTR({FIXED :COUNTD([Siast Current College Desc])})

                      THEN

                      ATTR(

                      IF (CONTAINS([Credential],"ACERT") OR CONTAINS([Credential],"APCERT") OR CONTAINS([Credential],"CERT")) THEN "Certificate"

                      ELSEIF (CONTAINS([Credential],"DIP") OR CONTAINS([Credential],"DIPC") OR CONTAINS([Credential],"ADIP")) THEN "Diploma"

                      ELSEIF (CONTAINS([Credential],"BACH") OR CONTAINS([Credential],"BSCN")) THEN "Degree"

                      ELSEIF (CONTAINS([Credential],"APPR1") OR CONTAINS([Credential],"APPR2") OR CONTAINS([Credential],"APPR3")

                          OR CONTAINS([Credential],"APPR4") OR CONTAINS([Credential],"APPUG")) THEN "Apprenticeship"

                      ELSEIF (CONTAINS([Credential],"MAST") OR CONTAINS([Credential],"PGCERT")) THEN "Post Grad"

                      ELSEIF [Credential] = "0" THEN "Non-Credentialed Programs"

                      ELSE "Adult Education"

                      END

                      )

                      ELSE

                        ATTR([Degree Desc])

                      END

                      • 8. Re: Cannot mix aggregate and non aggregate in if expressions
                        Jennifer Hysuick

                        I had someone clean this up for me and package it up.

                         

                        There are two worksheet in the attachment:

                        Total Enrolment is doing what I want it to do, but is using parameters, which I am trying to get away from for many reason, but the main one is that I can't "next" them to filter the a parameter based on what is picked in the other parameter.

                         

                        Total Enrolment (2) is the one that I am trying to get working.  My goal is that when All is selected in the School Filter that the results will look like the one above when All Schools is selected from the parameter.

                         

                        The calculation in question is Credential Calculation (1).  It is attached as it was in my last attempt using the Window_Count() suggestion.

                         

                        Thank you for all you help (to everyone).

                         

                        Jenn