9 Replies Latest reply on Sep 19, 2017 4:45 PM by Justin Larson

# Cannot Mix Aggregate and Non-Aggregate functions in custom calculation....

I am trying to fix the below calculation so my value returned only includes dollar values for styles marked as "flex" (a category which I uploaded from an excel document) and had dollar sales in 2017.

I understand these functions are aggregate and non-aggregate which is why my calculation is wrong.

Can anyone please suggest how I may fix the calculation to get my desired result? Thanks!

• ###### 1. Re: Cannot Mix Aggregate and Non-Aggregate functions in custom calculation....

Kathryn,

use ATTR(Year(Fiscal Month(Date)))

Thanks,

Michael

3 of 3 people found this helpful
• ###### 2. Re: Cannot Mix Aggregate and Non-Aggregate functions in custom calculation....

Hi

you need to wrap your Fiscal Monthly  [Date] in Attr()

Year is not an aggregation

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 3. Re: Cannot Mix Aggregate and Non-Aggregate functions in custom calculation....

It says the calculation is valid but does not return a dollar value:

• ###### 4. Re: Cannot Mix Aggregate and Non-Aggregate functions in custom calculation....

Kathryn, what the other Michael is suggesting gets both parts of your IF test to be at the same level of aggregation.  The ATTR() operator is very handy for these kinds of situations.

An alternative is to move where the SUM() function operates.

You can wrap the entire IF logic in SUM():

SUM(

IF (TEST1) AND (TEST2)

THEN [Shipped Sales]

)

Either of those should work.

• ###### 5. Re: Cannot Mix Aggregate and Non-Aggregate functions in custom calculation....

I'm suspicious of the YEAR logic.  You might have to make the value you're testing for into a text string instead of a number (i.e., wrap it in quotes).

What happens if you break the AND into its two constituent parts?  Do you get a sum if all you test for is the "Flex" value?  Same question, but for the "Year" value?

• ###### 6. Re: Cannot Mix Aggregate and Non-Aggregate functions in custom calculation....

That can be added with Format. Right click the new calculated field. Click Default Properties, then Number Format, choose currency, you will see a default prefix with '\$', click OK. Done.

Michael

2 of 2 people found this helpful
• ###### 7. Re: Cannot Mix Aggregate and Non-Aggregate functions in custom calculation....

That's not the problem, Michael.  The entire row is blank: that calculation is not returning any values at all.  So something in the calculation itself is out of whack.

• ###### 8. Re: Cannot Mix Aggregate and Non-Aggregate functions in custom calculation....

Michael,

Year() function returns a number and it is good, you don't need to make it a string.

I make a sample workbook in attachment in which I make a calculated field as "Furniture 2016 Sale":

IF ATTR([Category])='Furniture'

AND ATTR(YEAR([Order Date]))=2016

THEN SUM([Sales])

END

When I put this measure on the "text", only Sales in Furniture Category in 2016 is displayed. That is what I need.

Michael Ye

3 of 3 people found this helpful
• ###### 9. Re: Cannot Mix Aggregate and Non-Aggregate functions in custom calculation....

The difficulty here is that your category is from a blended datasource. This means in effect you don't have access to row-level information from the secondary source, only aggregates. This is why Tableau forced you to use ATTR() against your Category field. In your case, you need access to row level information because you are using Year at a row level AND Category at a row level. Wrapping YEAR([Date) in ATTR will only work if you filter the sheet down to 1 year, which will likely break the other calculations on your sheet (I'm assuming based on the way you've labeled them).

If you are using Tableau 10, you can bring the Category information in via Cross-Database Join, which will flatten the results into a single data set, and the calculation would be quite simple:

SUM(

IF Category = 'Flex'

AND YEAR([Fiscal Month (date)]) = 2017

THEN [Shipped Sales]

END

)

1 of 1 people found this helpful