3 Replies Latest reply on Jun 9, 2017 1:16 PM by Jim Dehner

    nested if statement for dimension

    Michael Damico

      I'm trying to figure out how I can nest a CASE/IF statement together based on a dimensions. This is what I am trying:

       

      if [Agency Name] = "US"

          then

              if

                  sum([EBIT YTD])/sum([Net Revenue YTD]) >=.20

                  THEN 'Meeting Expectations (>20%)'

              end

          ELSEIF

          [Agency Name] = "Canada"

          then

              if

                 sum([EBIT YTD])/sum([Net Revenue YTD]) <.20 and sum([EBIT YTD])/sum([Net Revenue YTD]) >=.13

                 THEN 'Below Expectations (13-20%)'

              end

      end

       

      I get an error about mixing aggregate and non-aggregate values. Where am I going wrong?

       

      Thanks!

      -Mike

        • 1. Re: nested if statement for dimension
          Marc-Anthony Di Biase

          The issue here is that you are combining an aggregate function (sum of ebit / sum of net revenue) with a non-aggregate result ('meeting expectations'). What I would recommend would be setting up a function that sums EBIT YTD, and Net Revenue YTD separate from this calculation, and calling these functions within the nested IF.

           

          If you would like to attach a copy of your workbook, I can try to work through the IF statement if my explanation was unclear.

          • 2. Re: nested if statement for dimension
            Elliott Stam

            Hi Michael,

             

            There's a good chance that all you need to do is wrap your [Agency Name] field in a MAX(), MIN(), or ATTR() function. It may seem like a weird solution, but the error you're getting reflects the stubborn reality of working with software logic. You are bumping into a general rule that states you can't combine aggregate and non-aggregate data, and in this case you are aggregating some fields (taking the SUM of some metrics) while not aggregating another field (Agency Name) within the formula.

             

            Wrapping [Agency Name] in MAX(), MIN(), or ATTR() won't actually change anything except that the aggregate/non-aggregate error will no longer be triggered because now ALL fields in the formula are "aggregated", technically. Since the calculations are evaluated at the row level, you shouldn't run into any trouble because the MAX() or MIN() of a single text value is... that text value! And since any given row only has one Agency Name associated with it, you should be good to go.

             

            Hope that helps,

             

            Elliott Stam - InterWorks

            • 3. Re: nested if statement for dimension
              Jim Dehner

              Hi

               

              Try using either MIN([Agency Name]) or Attr([Agency Name])

               

              they will not change your results just meet the requirement for an aggregate

               

              Let me know if this helps

              Jim

              1 of 1 people found this helpful