5 Replies Latest reply on May 31, 2017 6:36 AM by Nick Downs

    Aggregate data not working in calculation

    Nick Downs

      I recently started using tableau to create visualizations to help me pick fantasy baseball lineups.  I created a calculated field based on player statistics that I use to determine values for each player based on what hand the pitcher throws with and what ballpark they're playing in (either home or away).  I have a calculated field for each factor (right handed pitcher, left handed pitcher, Home, and Away.  To determine a players value for a particular day I add up the values from whichever two factors apply to them.  An example of this would be a player that is at home against a right handed pitcher.  I add the two values and get the players overall value for the day.  What I wanted to do is create a separate calculated field that automatically adds up all players' match up value for the day.  I have tried to use CASE by entering Case [matchup]  WHEN 'RA' THEN VALUE(RHP) + VALUE(AWAY).  In this calculation RA indicates that a hitter is playing a right handed pitcher in an Away stadium.  When I input the calculation I get the error regarding not being able to combine aggregate and non-aggregate data.  All I want to do is have the two values automatically added up, but I can't figure out any way around the error.  I'm not sure how well I explained this, but any help would be appreciated.

        • 1. Re: Aggregate data not working in calculation
          John Sobczak

          case attr([Matchup]) when 'RA' then [VALUE (RHP)] + [VALUE (AWAY)]

          end

          1 of 1 people found this helpful
          • 2. Re: Aggregate data not working in calculation
            Ben Neville

            Hi Nick - this is an issue which people struggle with at all difficulty levels. Even advanced users I run across are occasionally stumped by this, but the fix is fairly straightforward. The underlying issue is aggregation as Tableau suggests. If you have ANY fields aggregated in a calculation, EVERY field needs to be aggregated. This often results in users trying to aggregate a measure they didn't want, when the fix is usually to put the IF statement (or CASE statement) inside of an aggregation. Take yours, for example:

            CASE by entering Case [matchup]  WHEN 'RA' THEN VALUE(RHP) + VALUE(AWAY)

             

            The issue here is that RHP and VALUE(AWAY) are aggregated, whereas Matchup is not. So you either need to aggregate Matchup (not really possible in this instance) OR include the Away/RHP logic in the calculation itself. Try modifying VALUE(LHP) to be something like (see underlined parts for change):

            ORIGINAL: (((100*(SUM([Obp (Lhp)])*SUM([R (Lhp)])))*(100*(SUM([Avg (Lhp)])+SUM([Slg (Lhp)]))))*AVG([Hit Factor]))/AVG([Salary])

            NEW: (((100*(SUM(IF NOT ISNULL([Name (LHP) THEN [Obp (Lhp)] END)*SUM([R (Lhp)])))*(100*(SUM([Avg (Lhp)])+SUM([Slg (Lhp)]))))*AVG([Hit Factor]))/AVG([Salary])

             

            I'm not entirely convinced this is actually necessary, but think about what this is doing. This means that you're multiplying SUM([R (Lhp)]) by an aggregated field, which is appropriate as R (Lhp) has a SUM aggregation in front of it. Just cascade this technique through wherever it's needed. I think it may only be needed in this first part, as if it does not resolve, the answer should be 0 (or NULL in this case - if you want it to be a 0 for some reason, throw a ZN() around the entire calculation). If you are summing/dividing nulls or 0s, you wont get any output. If you're averaging and you don't want the zero values to average, you should make sure they resolve to NULL instead.

             

            Hopefully this is helpful, but if you get stuck somewhere, give us an update and we will be happy to provide more context.

             

            Also, for the sake of brevity, I often write my own calculation as a flag for these. It should resolve slightly faster in most instances, and it also keeps your calculations a bit neater. In this case you might create a field called [LHP?] with the calculation of:
            NOT ISNULL([Name (LHP)

             

            Your above calculation would then be:

            (((100*(SUM(IF [LHP?] = TRUE THEN [Obp (Lhp)] END)*SUM([R (Lhp)])))*(100*(SUM([Avg (Lhp)])+SUM([Slg (Lhp)]))))*AVG([Hit Factor]))/AVG([Salary])

             

             

             

             

             

             

             

             

            • 3. Re: Aggregate data not working in calculation
              Ben Neville

              Also, John's calculation will work so long as you only have a single matchup on a line/bar/circle/whatever your chart type is. If that's always true, then go with that as it's the easiest solution. If you might ever have 1 line for a pitcher or dot on a scatter plot, etc. and won't show every matchup as its own data point, you'd do better to go with my method as it's a bit more versatile.

              • 4. Re: Aggregate data not working in calculation
                Jim Dehner

                Hi Nick

                 

                Ok first create a parameter to input the right/left/home/Away

                 

                Then Create an if statement

                 

                IF [enter home away / r/ l] ="RA" then ([VALUE (AWAY)]) + ([VALUE (RHP)])

                elseif ([enter home away / r/ l])="LA" then ([VALUE (AWAY)]+ [VALUE (LHP)])

                elseif [enter home away / r/ l] ="RH" then ([VALUE (HOME)]) + ([VALUE (RHP)])

                elseif ([enter home away / r/ l])="LH" then ([VALUE (HOME)]+ [VALUE (LHP)])

                end

                 

                and you then drag the Combined Value to the viz - make it discrete (BTW I set the format to whole number no decimals

                 

                and you will end up with this

                 

                 

                Let me know if this helps

                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.

                • 5. Re: Aggregate data not working in calculation
                  Nick Downs

                  Thank you so much everyone that responded!  This small change actually ended up being all i needed.