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

# nested if statement for dimension

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

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

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

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