2 Replies Latest reply on Aug 1, 2016 3:01 AM by Gary Booher

# Help with "Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions" error

I have a database that includes calls my company makes on a daily basis.  To calculate the calls, I had to do an aggregate to eliminate duplicates to get the true call numbers.  Now i want to calculate the calls/month.  I am attempting to write an IF statement to take each years' total and divide by the months in that year.  For past years, that is easy as it is simply 12.  However, for the current year, I want it to divide by the current month number.  For example, if I run the report in June, it should divide by 6.  When I create my IF statement, I get the "Cannot mix..." error above.  Any suggestions on how to get around this?  Here is my attempt:

IF YEAR([Call Date]) = YEAR(TODAY())

THEN

COUNTD([Call Number])/(MONTH(TODAY()))

ELSE

COUNTD([Call Number])/MAX(YEAR([Call Date]))

END

Thanks in advance for any help.

Gary

• ###### 1. Re: Help with "Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions" error

Well, as it says - you are mixing two types of functions:

• ones that operate on individual rows (i.e. YEAR())
• ones that operate an all the rows in the scope (i.e. COUNTD())

As to workaround - usually people put MAX() or MIN() around non-aggregate.

1 of 1 people found this helpful
• ###### 2. Re: Help with "Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions" error

Thanks Dmitry.  I had tried using MAX but didn't have much luck.  After your response, I rethought how I used the MAX function and tried a different way.  My second attempt worked!  Thanks for answering and confirming the use of MAX was the correct tract.  The actual formula that worked was:

IF MAX(YEAR([Call Date])) = YEAR(TODAY())

THEN

[Distinct Cnt Call No]/MONTH(TODAY())

ELSE

[Distinct Cnt Call No]/12

END