Thanks Alex and Derek,
You both exposed the error of my ways. Tableau does does not allow the mix of aggregate data and non-aggregate data within an "IF" statement. That's been a hard one for me to allow to sink in. In my roadblock moment I found myself tinkering with my fix (and wondering why it wasn't working), which was wrapping my non-aggregated data with the "ATTR()" function.
[Score] is a calculated (and aggregated) field. And although Tableau excepts this calculation, it does not provide any data, just Null for each corresponding data point. Why this fundamentally does not work, I'm still unclear on. However as Alex pointed out, the fix was to write the calc like this:
and the aggregate the pill on the shelf with AVG().
I hope this helps other avoid the frustration I experienced.
To round this out - the first equation you had was an aggregate - akin to a formula you may have at the bottom of a column in Excel.
The input to attr can only be one value across the whole of the column (or partition with table calculations) - that is the purpose of attr. You were feeding 1 and 2 to attr which results in * which then returns null from your equation.
Hey, Robert. I find that I get trapped in the aggregate in an IF scenario as well. Do you think Tableau will be able to change this or is it really impossible to do?
Good question. However, I don't know that I'm qualified to even offer up an educated guess. I don't know where Tableau programmers are bound by the laws governing SQL and where they are not. If mixing aggregate and non-aggregate data in a "IF" statement is restricted by SQL, then it may not be something they can easily address.
We'll have to wait and see. It's fun to think about the direction Tableau may head over the next five years.
There isn't really any handling that needs doing - because the calls to the db (even to Excel) can produce different rows and columns, the concept of a flat file isn't there.
In Excel we may have a calculation on every row that also calls out to a referenced absolute cell (e.g. this row value/the sum at the bottom which is absolute)
This works because that flat file isn't changing - the row isn't suddenly going to change to a different level of aggregation. This is not the case for Tableau where calculations are more flexible.
Therefore we have to specify how to handle this - do we want to sum all the values now before we divide, or do we know that there will only ever be one value fed over (then we can use the attr aggregation)?
It takes a while to understand this, but this is the way it should work.