9 Replies Latest reply on May 11, 2016 9:42 AM by Mohit Sharma

    Mixing aggregate and non-aggregate measures in CASE statement - Help

      Hi,

      I’m new to Tableau and am slowly getting to grips with it.

       

      My current problem is that I have several measures in my workbook including Health Expenditure and Population and used these to create calculated measures such as Health Expenditure per capita.

       

      I wish to create a chart with Population on the X-axis and allow the user to select  another measure on the Y-axis.

       

      I am successfully able to create a parameter to dynamically change the Y-axis on a chart using the main measure (Health Expenditure), however, I would like to include the calculated measures in the parameter.

       

      When I try this, it does of course return the “cannot mix aggregate and non-aggregate comparisons or results  in ‘CASE’ expressions” error”

       

      Is there any way this can be done or should I be looking at doing the calculation in my original data source?

       

      Thanks

       

      J

        • 1. Re: Mixing aggregate and non-aggregate measures in CASE statement - Help
          Richard Leeke

          Post a sample workbook (using mocked-up data) showing the exact form of your calculation and someone will probably be able to help you get it going.  You often find that the ATTR() function can be helpful in these cases - but it's hard to know without seeing the exact scenario.

          • 2. Re: Mixing aggregate and non-aggregate measures in CASE statement - Help

            Hi

             

            Sample workbook attached .....as you will see creating a basic chart is fine, but using the aggregate (Health Expenditure per capita) in the CASE statement for a custom y-axis causes issues.

             

            Just wondering which is the best way to go about it :-)

             

            Thanks

            • 3. Re: Mixing aggregate and non-aggregate measures in CASE statement - Help
              Richard Leeke

              I just had a very quick look and made a couple of changes that stopped you getting any errors.  I haven't looked to see if it's giving the answer you want though.

               

              Basically I just put aggregate functions round the non-aggregate fields inside the CASE statement so that y-axis became an aggregate.  Then I replaced SUM(y-axis) with just y-axis on the scatter.

              • 4. Re: Mixing aggregate and non-aggregate measures in CASE statement - Help

                Many Thanks,

                 

                That works a treat - I think like many newbies, my brain gets caught in a loop thinking about 'disaggregating' fields rather than aggregating all the others.

                 

                You guys make it all seem so simple :-)

                • 5. Re: Mixing aggregate and non-aggregate measures in CASE statement - Help
                  Richard Leeke

                  It took me a long time to get my head around the "Mixing aggregate and non-aggregate" business - and I still have to stop and think about it sometimes.  But rest assured - it will become pretty much second-nature in most cases after a while.

                  • 6. Re: Mixing aggregate and non-aggregate measures in CASE statement - Help
                    Ryan K

                    Hi everyone,

                    I just ran into a similar problem - and solved it - so I may have something to add to this discussion. Here's a brief rundown of what I was trying to do:

                     

                    I have 2 companies that are identified by a branch code.

                    For each branch I have budgets, revenue, and expenses by month, further broken down by some other dimensions. Make sense? Think of this like a mini-income statement with some embellishment.

                     

                    I wanted to aggregate everything so that I could use a calculated field to calculate ROI. I needed everything to be aggregated so that I could perform this calculation at multiple rollup levels and at the grand total level. (BTW, If you don't aggregate everything by using SUM() or something similar your totals could end up being an average of averages - which is a very bad thing!). So, I needed some aggregate function to apply to my Branch dimension that would act kind of like a dummy function (not alter anything), yet also give me the flexibility to add additional Branches in the future. At first I tried MIN and MAX...but those only give me the ability to use 2 branches.

                     

                    Then, lo and behold, I found the ATTR function which I've never used before. IT WORKED! This served to "fool" Tableau into letting me use this formula without really aggregating anything. ATTR just returns the value you pass it if there's one unique value in the dataset and an asterisk if multiple. Perfect.

                     

                     

                    Anyhow, I hope this helps someone searching for a solution on the forums. For all that they've helped me I thought it'd be right to return the favor. Thanks, everyone!

                     

                    -Ryan

                    1 of 1 people found this helpful
                    • 7. Re: Mixing aggregate and non-aggregate measures in CASE statement - Help
                      guest contributor

                      Hi - I went through your examples trying to understand and see what you did.  When you choose Health Expenditure per capita as the Y axis- the Chart with custom y-axis chart does not display correctly.  It keeps the same chart as if you chose the Population.  Do you know why this is? 

                       

                      If I put all scenario's as a When and then use the Else statement kind of as a throw away, it captures all three scenarios/values for the Y-axis.  Maybe all three worked fine for you?  Or do you see the same thing?

                       

                      CASE [Choose Y axis]

                      WHEN "Health Expenditure (US$ billions)" THEN SUM([Health Expenditure (US$ billions)])

                      //aggregate below causing problems in CASE statement

                      WHEN "Population (millions)" THEN (SUM([Population (millions)]))

                      WHEN "Health Expenditure per capita" Then [Health Expenditure per capita]

                      ELSE

                      (0)

                      END

                      • 8. Re: Mixing aggregate and non-aggregate measures in CASE statement - Help
                        Richard Leeke

                        There was a stray bracket at the end of the WHEN condition which meant it would never match the parameter in the original calculation (WHEN "Health Expenditure per capita)").  I didn't notice that when I looked at it before. 

                         

                        CASE [Choose Y axis]

                        WHEN "Health Expenditure (US$ billions)" THEN [Health Expenditure (US$ billions)]

                        //aggregate below causing problems in CASE statement

                        WHEN "Health Expenditure per capita)" THEN [Health Expenditure per capita]

                        ELSE

                        [Population (millions)]

                        END

                        • 9. Re: Mixing aggregate and non-aggregate measures in CASE statement - Help
                          Mohit Sharma

                          Hi Richard,

                           

                          I was stuck with a similar issue of aggregate and non-aggregate comparisons in IF statement. Hope you can provide some guidance here. I am using following calculation in a field:

                           

                          IF [EventCode]= ('005')

                          THEN ((DATEDIFF('day', [Last Tracking Date], NOW())) +

                                 (IF (DATENAME('weekday', [Last Tracking Date]) = 'Saturday') THEN 1 ELSE 0 END) -

                                 (DATEDIFF('week', [Last Tracking Date], NOW()) * 2) -

                                 (IF (DATENAME('weekday', [Last Tracking Date]) = 'Sunday') THEN 1 ELSE 0 END) +

                                 (IF (DATENAME('weekday', NOW()) = 'Sunday') THEN 1 ELSE 0 END))

                          ELSEIF  ([EventCode]!=('017') OR [EventCode]!= ('038'))

                          THEN ((DATEDIFF('day', [First Event Time], NOW())) +

                                 (IF (DATENAME('weekday', [First Event Time]) = 'Saturday') THEN 1 ELSE 0 END) -

                                 (DATEDIFF('week', [First Event Time], NOW()) * 2) -

                                 (IF (DATENAME('weekday', [First Event Time]) = 'Sunday') THEN 1 ELSE 0 END) +

                                 (IF (DATENAME('weekday', NOW()) = 'Sunday') THEN 1 ELSE 0 END))

                          ELSE 0 END

                           

                          Here, [First Event Time] is a measure which has a table calculation in it. [First Event Time] is calculated as below:

                           

                          WINDOW_MIN(MIN( IF  [Event Sequence] = { FIXED [EShopPackageReference]: MIN([Event Sequence])} THEN [EventTime] END))

                           

                          Now I know that in my elseif statment NOW() and [First Event Time] are the two different comparisons throwing error. I can not aggregate the NOW() function and cannot deaggregate [First Event Time] field either.  I couldn't attach the sample workbook as the it is really big in size. Let me know if it is necessary to provide the tableau workbook. I'll try to sample it to some less data.

                           

                          Thanks,

                           

                          Mohit