5 Replies Latest reply on Jun 13, 2015 12:49 PM by Jonathan Drummey

    How can I have both aggregate and non-aggregate functions in tableau calculation?

    Quinn Francis

      My workbook is attached. Here is the breakdown.

      1. Start Probability and End Probability fields are used to define the start and end values for a given date.

      2. Probability Change uses these fields to determine if the probability went up or down for a given ID.

      3. Pipeline Stage uses Probability Change to define the category (Increase or Decrease) in probability.

       

      Problem: I am getting the error "Cannot Mix Aggregate and non-Aggregate augments in IF statement." I cannot find a way around this as I have applied ATTR and MAX or MIN to get successful calculation but then it turns Pipeline Stage into a measure and my defined categories are not useable. My workplace is not currently offering Tableau 9 until updates are made. Please let me know if Tableau 9 takes care of this issue I am having.

       

      Attached: Tableau 8 Workbook.

        • 1. Re: How can I have both aggregate and non-aggregate functions in tableau calculation?
          ailsa.zheng

          Hi Quinn,

           

          I noticed part of your calculated field says:

           

          ([Probability Change] = 1

           

          But how can Profitability Change equal a number when it can only result in "Down", "Up", and "No Change"?

          • 2. Re: How can I have both aggregate and non-aggregate functions in tableau calculation?
            Quinn Francis

            Thanks for pointing that out. I updated the attachement to reflect "Up" or "Down" in the Probability Change calculation. Tableau did not save this because I updated this from the 1 and -1 values after running into the error.

            • 3. Re: How can I have both aggregate and non-aggregate functions in tableau calculation?
              ailsa.zheng

              Hi Quinn,

               

              I found out where the error lies: the problem now is no longer with your Pipeline Stage calculation or your Probability Change calculation, but with your Start Probability and End Probability.

               

              For example, when I removed the MAX() function from your Start Probability and End Probability, this happens:

               

              datapopsup.PNG

              • 4. Re: How can I have both aggregate and non-aggregate functions in tableau calculation?
                Quinn Francis

                Yes, this is true. But the problem I am having is that I am only able to get the correct Start Pipeline and End Pipeline by making them Aggregate functions. If you now look on Sheet 8 and re-insert Start, End, and Probability Change, the Probability Change is incorrect when comparing the Start and End Probabilities. If I could avoid making them an aggregate function, that would solve my problem.

                • 5. Re: How can I have both aggregate and non-aggregate functions in tableau calculation?
                  Jonathan Drummey

                  I took a quick look at this and very likely v9 will solve your problems with level of detail expressions, in the meantime I believe there is likely a workaround in v8 and earlier using table calculations. However I was unable to get at too much for details because there are so many red pills, I don't know what the intent is to be able to help you.

                   

                  Here's a quick explanation of what I'm thinking. Prior to v9, Tableau had essentially 3 levels of calculation:

                   

                  - record level. These fields are computed for each and every record.

                  - aggregate. These are computed for each and every mark, where the mark is based on the dimension(s) in the view, otherwise known as the "viz Level of Detail" or "vizLOD".

                  - table calculation, where table calculations can be nested to perform up to 128 additional levels of aggregation. These are computed for each and every mark, the results depend on the addressing and partitioning of the table calculation.

                   

                  What it looks like you're trying to do is aggregate across _something_ to get correct Start Pipeline and End Pipeline, I'm guessing each PipelineID? Whatever that is, the way to express that intent in Tableau v8 and earlier is to build a view with the necessary dimension(s). Then if you need to do further aggregations across that you can use table calculations. The complication of table calculations is that the underlying aggregations may need to be at a coarser or finer granularity than the desired view, so there are various techniques required to deal with that since both table calculations and the regular aggregates are all computed in the same view.

                   

                  What Tableau v9 adds is Level of Detail expressions, they let us define aggregate calculations that effectively compute outside the view and therefore keep the view simpler while the LOD expression lets us specify the desired granularity for each aggregate.

                   

                  To help you further, we'd need to have a better sense of each level of aggregation you're trying to create.

                   

                  Jonathan

                  1 of 1 people found this helpful