2 Replies Latest reply on Jun 26, 2018 6:44 AM by Nicholas Zabilski

# Cant get formula to work "Cannot mix aggregate and non-aggregate results in if expressions" (Sorry if this is a basic question!)

So I really need to get this formula to work asap but I cant get rid of the errors.

Here it is:

IF [Lifecycle] = "Core"

OR [CO Status] = "CO"

THEN SUM([Fixed IT])+SUM([Fixed TW DC OH])+[STD Sales]+SUM([Current Store OH])

END

All of the variables after THEN are calculated fields (the ones with "fixed" in them are fixed LOD expressions with an average function like: { FIXED [Generic Article]:AVG([Stock in transit])}.

The [STD sales] Variable is also a calculated field and is already aggregated with a sum.

I cannot get this to work! I have tried looking up the error, but couldn't seem to find a useful case using an "OR" and "ELSE" together.

SUM([Fixed IT])+SUM([Fixed TW DC OH])+[STD Sales]+SUM([Current Store OH]) works perfectly fine on it's own..but when I add the IF, everything errors out on me.

What am I missing here?

Thank you so much!

• ###### 1. Re: Cant get formula to work "Cannot mix aggregate and non-aggregate results in if expressions" (Sorry if this is a basic question!)

Good morning

the issue is that sum() is an aggregate so every variable in the formula needs to be aggregated - in the first cclause you need to aggregate lifecycle and CO Status  with Min(), MAX or Attr

IF attr( [Lifecycle] )= "Core"

OR  attr([CO Status] )= "CO"

THEN SUM([Fixed IT])+SUM([Fixed TW DC OH])+[STD Sales]+SUM([Current Store OH])