1 2 Previous Next 15 Replies Latest reply on Mar 15, 2019 10:04 AM by Jim Dehner

    "cannot mix aggregate and non-aggregate" error.

    Jenifer Lee

      Hello!

       

      I am very new to Tableau and I really want to understand more about aggregate and non-aggregate arguments.

      I am currently converting a multi - step, multi-sheet excel file into tableau and everything has been going great except for this last step.

       

       

      1 I have created a new calculation (METRIC under Measures) to calculate the safety metric our company has come up with by adding the column commercial weight if they are at a certain location/facility.

      SUM( [Commercial Wt])

       

      2 I have created another calculation (Operations under measures) to calculate the total number of operations by adding two other columns together.

      [Air Carrier]+[Air Taxi]

       

      These two calculations have other dependencies (which I don't think matters much).

      I would like to find the normalized safety metric by:

      using the first calculation and multiplying it by 1000000 and dividing it by the total number of operations (calc 2)

       

      when I try to create a new calculation and type in the formula:

      [METRIC]*1000000/[Operations]

      the error : "cannot mix aggregate and non-aggregate arguments" pop up.

       

      Is there a easy fix to this step? I'm sure its super simple and I am misunderstanding the arguments. Please help, I've been stuck for two days.

      I hope this made sense.

        • 1. Re: "cannot mix aggregate and non-aggregate" error.
          Robert Breen

          Hello Jenifer,

           

          Since you have an aggregation (SUM) in the metric calculation, you will also need to aggregate the operations calculation to be able to use them in the same calculated field.

           

          solution 1:

           

          update operations to be:   sum([Air Carrier]+[Air Taxi])

           

           

          solution 2:

           

          update your final calculation to be: [METRIC]*1000000/sum([Operations])

           

          Hope this helps!


          Robert

          1 of 1 people found this helpful
          • 2. Re: "cannot mix aggregate and non-aggregate" error.
            Ewald Hofman

            Think of it in this way. The data is presented to Tableau as a table. [Air Carrier] And [Air Taxi] are two columns in that table. The Operations calc is thus another column in the data source behind the viz.

            The METRIC however is an aggregate for whatever dimensions you have added to the Columns and Rows shelf, and is not part of the data source behind the viz.

             

            Because they are different (aggregated for METRIC and non-aggegated for operations), you can't combine them. You will need to make your Operations also aggregated. There are multiple ways

             

            1. Update your Operations metric to SUM([Air Carrier] + [Air Taxi]) to create a similar aggregation. Now you can use these two calculated fields to calculate your safety metric.

            2. Change your safety metric to [METRIC]*1000000/SUM([Operations])

            1 of 1 people found this helpful
            • 3. Re: "cannot mix aggregate and non-aggregate" error.
              Jim Dehner

              Hi

              first welcome to Tableau and the forum

               

              aggregates and non-aggregates - I am going to assume that you come to tableau with experience with spreadsheet calculations

               

              database oriented systems are very different form that - the dimensions are used to categorized data and the measures are the actual values

              when you bring a dimension/measure to the canvas the measure is aggregated - it like taking a whole column of spreadsheet data and aggregating it -

              so far no problem - BUT then every dimension or measure in the viz also needs to be aggregated at the same level -

              Pretty easy to understand with numbers but you also have to aggregate dimensions including strings (text) and dates

               

              Attr() for attribute is an aggregate function that can be used - but take care it doesn't compute and can result in *  meaning that the aggregation does not contain unique values

              min(), max() are also aggregates - dates are sequential and strings ar alphanumeric sequence so in your case you probably need to attr([Air Carrier])+attr([Air Taxi])

              and sum(metric) and sum(operations)  but that is only a guess without seeing your data and twbx workbook

               

              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
              • 4. Re: "cannot mix aggregate and non-aggregate" error.
                Jenifer Lee

                What if I do not want the sum of the whole column added together but for each row specifically.

                 

                When I try either solution 1 or solution 2 it is not getting the correct numbers because I'm guessing that it is adding the total of that whole column -

                 

                for example, if i add

                 

                solution 2:

                update your final calculation to be: [METRIC]*1000000/sum([Operations])

                 

                it is dividing by the total number of operations not by the operations for that month.

                • 5. Re: "cannot mix aggregate and non-aggregate" error.
                  Jenifer Lee

                  By the way, thank you very much for the fast response and help!!!!!

                  • 6. Re: "cannot mix aggregate and non-aggregate" error.
                    Jim Dehner

                    Jennifer

                    Thanks

                    the sum() used on measures is also filtered by the other dimensions and measures on the sheet -

                    think of it this way - you want to find the orders associated with a customer and order number - so you put the customer and the order in the vis and sum the sales because there were several lines on the order -

                     

                    you are still getting only the sales on that order but to get them there were 2 records in the data that were added together

                     

                    had there been only one record you would still use sum() but there would be only one record to total

                     

                    Jim

                    • 7. Re: "cannot mix aggregate and non-aggregate" error.
                      Jenifer Lee

                      What if I do not want the sum of the whole column added together but for each row specifically.

                       

                      When I try either solution 1 or solution 2 it is not getting the correct numbers because I'm guessing that it is adding the total of that whole column -

                       

                      for example, if i add

                       

                      solution 2:

                      update your final calculation to be: [METRIC]*1000000/sum([Operations])

                       

                      it is dividing by the total number of operations not by the operations for that month.

                      • 8. Re: "cannot mix aggregate and non-aggregate" error.
                        Ewald Hofman

                        If you want that, you will need LOD expressions, such as

                        Overview: Level of Detail Expressions - Tableau

                         

                        {FIXED DATETRUNC('month', MyDate) : [METRIC]*1000000/sum([Operations]) }

                        • 9. Re: "cannot mix aggregate and non-aggregate" error.
                          Jim Dehner

                          Jennifer - it is difficult to see what is going on with out you book - please post it when with some dummy data -

                           

                          could be a number of thinks - is all the data from a single source/ - houw is the date incorporated in the viz -

                          you could end up with an LOD like    {fixed mont(date),year(date) :sum(operations)}  but that would depend on the data, the data structure and other calculations in the viz

                          Jim

                          • 10. Re: "cannot mix aggregate and non-aggregate" error.
                            Jenifer Lee

                            1.PNG2.PNG3.PNG4.PNG5.PNG6.PNG7.PNG

                             

                            Hi,

                            I am sorry for all the screenshots. I am not in the office and had someone send me screenshots and this is the best I can do at the moment.

                             

                            1. Showing the Data Source and the columns.

                            2. This is the graph we want with the New normalized metric (METRIC 2) , currently using the METRIC calculated by our company.

                            3. Table showing the correct number of operations and the numbers for "METRIC"
                            4. The "CANNOT MIX AGGREGATE AND NON-AGGREGATE" ERROR when in the METRIC2 pill

                            5. METRIC pill formula

                            6. Operation pill formula

                            7. When changing the Operation pill's formula to SUM([Air Carrier] + [Air Taxi]) the numbers change.

                             

                            So in the data set there are many events with the same month and year. I believe it is adding all the number of operations of each data entry for the same month and year as shown in the data.

                            I would like for the unique number of operations for that month and year.

                             

                             

                            If these screenshots do not help I can post the dummy data next time i'm in the office.

                            If you need other screenshots I can ask for her to take some so please feel free.

                             

                            Thank you all again for all the help for the noob- tableau-er

                            • 11. Re: "cannot mix aggregate and non-aggregate" error.
                              Jenifer Lee

                              please see comment below!

                              • 12. Re: "cannot mix aggregate and non-aggregate" error.
                                Jenifer Lee

                                I have attached a sample workbook

                                • 13. Re: "cannot mix aggregate and non-aggregate" error.
                                  Ewald Hofman

                                  In your screenshot you use the ATTR(Operations) to put on your Viz. Have you tried to update your formula to

                                   

                                  [Metric] * 1000000 / ATTR([Operations])

                                  1 of 1 people found this helpful
                                  • 14. Re: "cannot mix aggregate and non-aggregate" error.
                                    Jenifer Lee

                                    Oh wow!!! It worked!! Thank you so much!!!!

                                    1 2 Previous Next