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

 

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 .

Meanwhile 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

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

 

Comments

There are no entries yet.
Please enter the code
* Required fields

Subscribe to our mailing list

* indicates required

Looking for Corporate Training ? Reach out to us at Akriti.Lal@instrovate.com

Reach out to us if you are looking for Corporate Training to Build The Next Generation Analytical Workforce with an in-depth understanding of  Exploratory Data Analysis , Data Visualisation, Data Analytics , AI First , Machine Learning & Deep Learning Training & Consulting helping them to take Data Informed Decision at each stage of the business. 

We understand that At the present times , the Entire Industry is in a Tranformation stage with the Softwares  being rebuilt with Artificial Intelligence Capabilities .

We need SMART WORKFORCE for the SMART SOFTWARES to reap the maximum return . 

 

Whatsapp at +91-9953805788 or email at - akriti.lal@instrovate.com if you would like to know more . 

 

  • Corporate Tableau Training in Gurgaon
  • Corporate Data Analytics Training in Gurgaon
  • Corporate Microsoft Power BI Training in Gurgaon
  • Corporate Microstrategy Training in Gurgaon
  • Corporate Google Data Studio Training in Gurgaon
  • Corporate Python Training in Gurgaon
  • Corporate Advance Analytics in R Programming Training in Gurgaon
  • Corporate Machine Learning Training in Gurgaon
  • Corporate Deep Learning Training in Gurgaon
  • Corporate Data Visualization Training in Gurgaon

 

Address

Instrovate Technologies

Noida, Gurgaon

 

akriti.lal@instrovate.com 

Hit Your Refresh Button To Rise Higher
Print Print | Sitemap
All rights reserved @ Instrovate Technologies
GSTIN : 09AAECI6862K1Z1

Terms Of Services    Privacy Policy    Disclaimer     Refund Policy    


Call

E-mail