9 Replies Latest reply on Dec 22, 2016 1:21 PM by Tableaunew learning

# Can't mix aggregate and non aggregate

I am using two calculated fields to achieve desired results..

1) Find maximum year from current data set

maxyear=max(year)

2) when year equals to maxyear then returns discounts

If Attr(Year)=maxyear then sum(discounts) else 0 end

if doesn't give me any result.

Thanks.

• ###### 1. Re: Can't mix aggregate and non aggregate

MAX(year) won't give you the max year for the entire data set.  It will give you the max year in each cell in the table.

You'll want to do some LOD that looks at the entire table.  Something like:

{ EXCLUDE [dimension 1] ([dimension 2], ...)  : MAX([Year])  }

You'll have to play with it, but what this is doing is telling Tableau not to take the various dimensions on the sheet into account.  Do it for all rows in the sheet's table.

If you want to find the max year in your entire data source, rather than in the data set on the sheet (if you have any filters, these will be different sets of data), then do:

{  FIXED  :  MAX([Year])  }

INCLUDE/EXCLUDE function AFTER the filters have engaged.  FIXED is evaluated before filters.

1 of 1 people found this helpful
• ###### 2. Re: Can't mix aggregate and non aggregate

Try

sum( if  ATTR(Year)= {  FIXED  :  MAX([Year])  } then discount else 0 end )

• ###### 3. Re: Can't mix aggregate and non aggregate

i dont have any other columns in my sheet. I just want to display what is current year discounts in one sheet and last year discounts in other sheets and want to bring these two in one dashboard.

So for that i wrote Max(year). It gives me write result... but when i do another calculation to find total discounts of max year, it gives me error as can't mix aggregate and non aggregate at this line:

If Attr(Year)=maxyear then sum(discounts) else 0 end

• ###### 4. Re: Can't mix aggregate and non aggregate

Post a sample workbook.  When you do, let me know what version of Tableau your workbook is.

• ###### 5. Re: Can't mix aggregate and non aggregate

if I understood your  requirement correctly , you want sum of discount for max(Year) right .

can you try this :

if  attr(Year)= MAX([Year])  then sum(discount) else 0 end

this works fine for me

• ###### 6. Re: Can't mix aggregate and non aggregate

This worked... however i want to calculate for last year ...in current year there are 6 months , so i want to calculate for 6 months only for 2015 also... How can i do?

• ###### 7. Re: Can't mix aggregate and non aggregate

See attached.

• ###### 8. Re: Can't mix aggregate and non aggregate

If that's the number you want to see, we can talk about how I did that.

• ###### 9. Re: Can't mix aggregate and non aggregate

Worked.. i understand that too..make sense