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

    Gary Booher

      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