1 of 1 people found this helpful
case attr([Matchup]) when 'RA' then [VALUE (RHP)] + [VALUE (AWAY)]
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])
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.
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)])
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
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.
tableau mlb data 5-29.twbx 804.7 KB
Thank you so much everyone that responded! This small change actually ended up being all i needed.