10 Replies Latest reply on Sep 22, 2018 1:23 AM by kshitij.nagarkar

# Cannot mix aggregate and non-aggregate comparisons or results in 'if' expression

Hi Everyone,

I am making a calculated field but its throwing error that "cannot mix aggregate and non-aggregate comparisons or results in 'if' expression".

My calculated field is:

IF [Open Dt (Month / Year)] <= MAX([Open Dt (Month / Year)])

THEN [Balance]

ELSE NULL

END

I know we cannot mix aggregate and non aggregate in an 'if' but I want to use this logic only:

IF [Open Dt (Month / Year)] <= MAX([Open Dt (Month / Year)])...

How do I proceed with that ?

• ###### 1. Re: Cannot mix aggregate and non-aggregate comparisons or results in 'if' expression

Without seeing data at all, but you mean this?  Using LOD expression..

Thanks,

Shin

IF [Open Dt (Month / Year)] <= { fixed :   MAX([Open Dt (Month / Year)])   }

THEN [Balance]

ELSE NULL

END

• ###### 2. Re: Cannot mix aggregate and non-aggregate comparisons or results in 'if' expression

Hi Kshitij,

Have you tried

IF ATTR([Open Dt (Month / Year)]) <= MAX([Open Dt (Month / Year)])

THEN [Balance]

ELSE NULL

END

HTH

Peter

• ###### 3. Re: Cannot mix aggregate and non-aggregate comparisons or results in 'if' expression

Thank you guys but that thing didn't worked out with your mentioned code.

So let me elaborate,

This image is my open_dt (its a custom date, discrete dimension).

So basically I have a average line passing through the dates beyond June 2015 but I wanted that average line to be till the highest date (i.e. in this case its June 2015).

That's why I am finding out whether every date is less than the max date and if yes then balance or else null.

So with this logic I am trying to find the highest data(i.e. June 2015) and then comparing all the dates with the highest date

IF [Open Dt (Month / Year)] <= MAX([Open Dt (Month / Year)])

THEN [Balance]

• ###### 4. Re: Cannot mix aggregate and non-aggregate comparisons or results in 'if' expression

I just saw an article posted - Understand And  Fix  Tableau Error “Cannot Mix Aggregate and Non Aggregate" .

• ###### 5. Re: Cannot mix aggregate and non-aggregate comparisons or results in 'if' expression

IF [Open Dt (Month / Year)] <= {MAX([Open Dt (Month / Year)])}

THEN [Balance] end

• ###### 6. Re: Cannot mix aggregate and non-aggregate comparisons or results in 'if' expression

Hi,

You may try this,

Put your own date field in calculation

THEN [Balance] end

Let us know if this help.

Mahfooj

• ###### 7. Re: Cannot mix aggregate and non-aggregate comparisons or results in 'if' expression

Thanks for your reply guys, nothing worked for my scenario as its saying cannot mix aggregate and non aggregate fields.

• ###### 8. Re: Cannot mix aggregate and non-aggregate comparisons or results in 'if' expression

Kindly share some sample mock up data in excel or packaged workbook else it will be very difficult for us to give you any solution on the fly.

• ###### 9. Re: Cannot mix aggregate and non-aggregate comparisons or results in 'if' expression

Hi ,

You can try below logic:

Flag : if [Order Date] < {MAX([Order Date])} then "True" END

• ###### 10. Re: Cannot mix aggregate and non-aggregate comparisons or results in 'if' expression

Thanks Guys,

I was able to come with the solution.
I added the order_date to the context apart from the calculated field