5 Replies Latest reply on Sep 28, 2018 6:45 AM by Ryan Hawkins

# Cannot mix aggregate and non-aggregate comparisons or results in IF expressions

Hi guys,

I am having issues with the below formula. I keep getting the "Cannot mix aggregate and non-aggregate comparisons or results in IF expressions" error

IF [Dates Selected]="Last Year" THEN [Total Transactions]

ELSE 0

END

[Total Transactions] is a calculated field = [Transactions Gross]+[Hotel - Transactions]

where

[Transactions Gross] = IFNULL(COUNTD([Document Number]),0)

[Hotel - Transactions] = IFNULL(SUM([BookingStatusID]),0)

Is there anyway i can get the calculation to work?

Many Thanks!

• ###### 1. Re: Cannot mix aggregate and non-aggregate comparisons or results in IF expressions

Hi Ryan

IF Attr([Dates Selected])="Last Year" THEN [Total Transactions]

ELSE 0

END

Thanks

sankar

• ###### 2. Re: Cannot mix aggregate and non-aggregate comparisons or results in IF expressions

Hi Sankarmagesh

I did try that but then it just returns 0

Thanks

Ryan

• ###### 3. Re: Cannot mix aggregate and non-aggregate comparisons or results in IF expressions

try this:

IF  MAX([Dates Selected])="Last Year" THEN [Total Transactions]

ELSE 0

END

• ###### 4. Re: Cannot mix aggregate and non-aggregate comparisons or results in IF expressions

Hi Ryan,

What's "Dates Selected" here?? Is it a calculated field/parameter??

• ###### 5. Re: Cannot mix aggregate and non-aggregate comparisons or results in IF expressions

It is a mix of both to be honest. [Date From] and [Date To] are parameters. Then [Date From LY] and [Date To LY] are calculated fields (365 days less than Date From and Date To)

Date Selected

IF [DateUsed]>=[Date From LY] AND [DateUsed]<=[Date To LY] THEN "Last Year"

ELSEIF [DateUsed]>=[Date From] AND [DateUsed]<=[Date To] THEN "This Year"

ELSE "Ignore"

END

Thanks

Ryan