## Cannot Mix Aggregate and Non-aggregate

One of the most frequent questions we see on the Forum results from trying mixing aggregate and non-aggregates in the same calculation – But why is that a problem and how do you resolve it –

It's just what we'd expect from a spreadsheet calculator

Tableau aggregates measures as they are brought to the viz so the individual profit percentages are summed in the viz – the problem started with the calculation – Sales and Profit need to be aggregated in the calculation

The message simply means that if one dimension or measure is aggregated in a calculation then ALL the measures and dimensions in the calculation must be aggregated

– But sometimes it is not easy to see which dimension or measures need to be aggregated. See the following examples:

**1-Table Calculations**

Table calculations are aggregations so other measures in the calculation need to be aggregated too – here Difference is a table calculation so Sales needs to be aggregated :

** **

**2-Embedded Dates**

Often dates are embedded in a calculation that includes an aggregation –

Here Sales in aggregated so Order Date and Ship Date need to be aggregated – Yes aggregate Dates – sure Dates and string (text) dimension can be aggregated with Attr(), Min() or Max() – your choice will depend on the analysis you are doing

This is just one way around the problem

** **

** **

**3 Aggregations caused by LOD's**

Sometimes, dimensions within a LOD cause the problem – The argument in a LOD must be aggregated – here Sales is aggregated but Category is not

There are 2 solutions – the first is to use a Min() or Max to aggregate the Category (Note: Attr() can not be used in a LOD)

**4 Using a LOD to resolve an aggregation problem**

**5 Value at Max Date**

An alternative would be to use a table calculation (here Last()) but it also returns an error

This can be resolved by aggregating Sales

**The process to Identify the Aggregate**

- Open the calculation and drag the measures to the Marks card –

- IF they show up with an AGG() then they are already aggregated –

- If Tableau tried to aggregate them with SUM() then they are not aggregated and you will need to decide which aggregation best fits your analysis

Hope this helps – If you have specific examples where you need some help feel free to ping me here or add a post to the Forum

Enjoy

Jim

## 0 Responses

Great resource for teaching. Thanks Jim!

Thanks Ann – I'm doing a series on FAQ's from the Forum – stop back – the next is on Nulls

Thanks for sharing Jim. It really helpful.

Excellent post Jim – thank you!

I understand the issue, but it still catches me. Your tips to identify what are aggregates or not were particularly useful.

I recently voted on this idea to visually differentiate between aggregates and non-aggregates: https://community.tableau.com/ideas/10853

Hi Jim,

I wonder if you can help me understand and resolve a problem I am struggling with. I have about 10 years of data joined by date at the day level. For three different columns (say A, B and C) I have the a value that corresponds to the value on that date.

I have a bar chart display that has the date grouped by year and I want to show the "total" value from each of A,B and C that corresponds with the last day of that year where I have a date. So – for 2011 it might be 30-Dec-11 as that is the last day I have a value in that year. I know this because I have created MAX([Date]) and filtered by that date.

I simplistically thought I would be able to grab the corresponding value from A, B or C that exists in the column for the MAX(Date) at the year level, however, what I get is SUM([A] etc. ) which is the sum of all the values in A up until the last day of the year. A huge number!

I've tried, LOOKUP with INDEX and LAST etc. in a calculated field using MAX([Date])but am blocked in grabbing this atomic value from the column as it requires an aggregated value (SUM([Total])). I can resolve this, pretty sure, by creating a new column in my data source that shows only the additions or subtractions in A, B or C and not their "current" value for that date. In this case I think the SUM([A]) will correspond, but I figure there is more likely something I am fundamentally missing to implement this with the data structured as is.

Is my explanation clear? and if you have a moment, do you see something obvious you could point out to me?

Hi

Thanks great question – just reading the post it sounds like the data need to be pivoted – but without seeing the actual workbook it is a little difficult to provide a specific solution- Suggest you post your question on the Forum at https://community.tableau.com/community/forums and include your TWBX workbook –

Thanks

This comment has been removed by the author.