14 Replies Latest reply on May 19, 2016 12:33 AM by siva goberu

# cannot mix aggregate and non-aggregate comparisons or results in IF Functions

Hi  All,

I connected to google analytics data source and trying to generate reports.

when i am trying to run the calculation as shown below .

Calculation:

IF

([Date]) >= [start_date] AND

([Date]) <= [Date_Parameter]

THEN

([Bounce Rate])

END

ERROR  : (cannot mix aggregate and non-aggregate comparisons or results in IF Functions)

I tried with ATTR(Expression) for the calculation,

IF

ATTR([Date]) >= [start_date] AND

ATTR([Date]) <= [Date_Parameter]

THEN

([Bounce Rate])

END

Calculation is valid but calculation field is not displaying any data

Fields Description :

Date- Date Field.

Bounce Rate - Aggregated Field

start_date -  DATETRUNC('month', [Date_Parameter])

Date_Parameter - Parameter Control.

Regards,

Siva

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

Hi ,

A sample workbook will help.

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

Bounce Rate is a calculated field I guess, Can you show me the formula? I would suggest just remove the aggregated function which you're using in the formula and try. Lets assume your current formula of Bounce rate is like this:  sum([ABC])/sum([xyz])

Now just remove the agg function sum() from the fomula : [ABC]/[xyz]

so your final calc field will be like this:

IF

([Date]) >= [start_date] AND

([Date]) <= [Date_Parameter]

THEN

[ABC]/[xyz] // without agg function

END

Feel free to ask if you've any query.

Mahfooj

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

Hi Mahfooj,

Thanks for the quick response!

This is my bounce rate formula:

((SUM([Bounces]))/(SUM([Sessions])))*100)

If i remove SUM() from the formula the value gets changed.

so i cant remove SUM() from the formula.

Can you suggest me some other way to resolve this.

Regards,

Siva

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

Meanwhile you can try this. When you drag below calc field it will be agg with sum() then change the format of this calc field. By default it will be automatic to number just change it to percentage.

IF

([Date]) >= [start_date] AND

([Date]) <= [Date_Parameter]

THEN

[Bounces]/[Sessions]

END

Let me know if you've any query.

Mahfooj

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

Hi Mahfooj,

Thanks for the response!

If i need the value in % i can set percentage , but the problem is with calculation field.

Calculation field:

current_yr_bounce_rate

Regards,

Siva G

• ###### 6. Re: cannot mix aggregate and non-aggregate comparisons or results in IF Functions

Hi,

PFA

Regards,

Siva G

• ###### 7. Re: cannot mix aggregate and non-aggregate comparisons or results in IF Functions

If you're unable to attach your workbook then just reply in below mentioned post. I've already raised this issue.

If possible then you can share the screen shot of your data in excel format(cross tab).

• ###### 8. Re: cannot mix aggregate and non-aggregate comparisons or results in IF Functions

Hi,

regards,

Siva

• ###### 9. Re: cannot mix aggregate and non-aggregate comparisons or results in IF Functions

Hi,

regards,

Siva

• ###### 10. Re: cannot mix aggregate and non-aggregate comparisons or results in IF Functions

The problem is that your logical check (The IF) is a row level comparison.

You could most likely do the following

SUM(

IF

([Date]) >= [start_date] AND

([Date]) <= [Date_Parameter]

THEN

[Bounces]

END

)

/

SUM(

IF

([Date]) >= [start_date] AND

([Date]) <= [Date_Parameter]

THEN

[Sessions]

END)

* 100

• ###### 11. Re: cannot mix aggregate and non-aggregate comparisons or results in IF Functions

Hi Morten,

Thanks for the response!

SUM(

IF

([Date]) >= [start_date] AND

([Date]) <= [Date_Parameter]

THEN

[Bounces] (Bounce rate)

END

)

in this calculation i use bounce rate instead of bounces , which is a aggregated field.

I am getting the same error.

This is my bounce rate formula:

((SUM([Bounces]))/(SUM([Sessions])))*100)

Regards,

Siva

• ###### 12. Re: cannot mix aggregate and non-aggregate comparisons or results in IF Functions

I substituted your "Start_date" with "DATEADD('day',-1,Today())"  (Which is just yesterday) to demonstrate my point

The date_Parameter is set to Today (2016-05-18)

2 of 2 people found this helpful
• ###### 13. Re: cannot mix aggregate and non-aggregate comparisons or results in IF Functions

Hi Morten,

Thanks for the response , i will try with the calculation which u have send.

Let u know if any queries.

Regards,

Siva G

• ###### 14. Re: cannot mix aggregate and non-aggregate comparisons or results in IF Functions

Hi Morten,

Calculation is working fine as expected.

Giving the correct result.

Thanks for the effort.

Regards,

Siva