1 Reply Latest reply on Oct 13, 2018 2:33 PM by patrick.byrne.0

    Understand And  Fix  Tableau Error “Cannot Mix Aggregate and Non Aggregate"

    Akriti Lal

      Understand And  Fix “Cannot Mix Aggregate and Non Aggregate “  Error

       

      In order to understand why do we get “Cannot Mix Aggregate and Non Aggregate “error and how to fix it, Let us take a Use case.

      In this example we are going to analyze

      1) The last 4 months of the Data set that we have

      2) Add a parameter to the view so that the Client can choose the number of months he wants to analyze together.

      We will be using the Sample Superstore Data set in order to save our effort to understand the data set. 

      Now what we want to do here is - instead of showing the client the entire dates in the data set; they just analyze the Last 4 months. Sort Your data by Dates and you can see the last months on your data set .

      So, in this case it’s going to be just September 2014 ,October 2014, November 2014 and December 2014.

       






      Create calculated fields to show the value:

      1) 1stLast Month - this should show the last month in the data set: Now as we understand the use case ,  If we try to create the Formula it would be difference between the Current Month and the Maximum Month that we have . If we write it, it would be as below. But Tableau shows an error for this calculation

       

      If you click on the Error drop down it shows - “Cannot Mix Aggregate and Non Aggregate “

      We get this error because we are trying to do calculation at Different Level of Details. In order to fix this  “Cannot Mix Aggregate and Non Aggregate “ , we will have to use the LOD function. 

      LOD is a very important & interesting topic and you can visit the LOD Knowledge Material to Understand it more , but as a quick fix for this error , what you need to do is put the aggregate function inside curly braces i.e. the Max() function that we use should be inside the curly braces  like below :

       

      This Fixes Your Issue. So Just By Adding Curly Braces you can get Rid of the error.   So if you were just looking for the fix for “Aggregate & Non Aggregate error” you are done and can leave the blog . However for the continuity and in order to finish our use case , we will continue with our Analytics and move ahead.

      So Now , Similarly we can calculate for the 2nd, 3rd , 4th Last Month

      2) 2ndLast Month - this shows the 2nd last month in the data set

      3) 3rdLast Month - this shows the 3rd last month in the data set

      4) 4thLast Month - this shows the 4th last month in the data set

      Now The formula to find 2nd Last Month would be

       

      If you see the last viz we do not have a structured Axis. As in it is not giving us any idea of which are the months that we are looking at. So, lets create a calculated field called Last 4 Months? This will be a Boolean. (Click on the field Last 4 Months in the Dimensions in the Data pane and hit on Edit to see the formula.)

      Put Order date (Month Continuous) to Columns & Sales to Rows. Add the Boolean Field Last 4 Months? to the filter & choose "True".

       

      But if you see this Viz it is a Static one. That means we can only see the last 4 months. Wouldn’t it be great if the client had the flexibility of choosing Last N months?

      So, let’s move ahead and make it Dynamic by adding a parameter to it.

      Craete a parameter Last N days. Data type- Integer. Allowable Value- All

      Go to the Boolean Calculated field Last 4 Months and edit it to replace a constant value of the parameter.

      I have created a new field Last N Months? with the formula: DATEDIFF('month',DATETRUNC('month', [Order Date]),

      {MAX(DATETRUNC('month', [Order Date]))})< [Last N Months]

      Bring this Boolean Calculated Field in the Filter Shelf and choose True.

      Bring the parameter to the sheet by Show Parameter Control.

      Now you can sees the Last N days that you choose



       

      Now if you chose parameter value as – 10 you can see the last 10 months as below