If you want to know who should Learn Tableau and Why , get the answer to your question based on my experience with hundreds of professioinals during my tableau training in Gurgaon , Noida , Bangalore , Ranchi , Mumbai , Pune and other parts of India. You can read my blog here – Best Tableau Training in Gurgaon

During my Corporate Tableau Training in Gurgaon , i get questions many time regarding Error “Cannot Mix Aggregate and Non Aggregate” in Tableau .

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.

If you want to know who should Learn Tableau and Why , get the answer to your question based on my experience with hundreds of professioinals during my tableau training in Gurgaon , Noida , Bangalore , Ranchi , Mumbai , Pune and other parts of India. You can read my blog here – Tableau Training in Gurgaon

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 my 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

If you want to know who should Learn Tableau and Why , get the answer to your question based on my experience with hundreds of professioinals during my tableau training in Gurgaon , Noida , Bangalore , Ranchi , Mumbai , Pune and other parts of India. You can read my blog here – Best Tableau Training in Gurgaon

For any further questions or if you are looking for a 3 Days , 4days or 5 Days Coproate Training to bring your professional up to speed immediately reach out to me at – https://www.linkedin.com/in/akritipurbey

or whatsapp at – +91-9953805788