Tableau's Field Calculations — Sometimes a Mystery
Tableau sometimes does things in a way that makes sense from a particular technical perspective, but leaves non-technical people bewildered, wondering what happened. If they're lucky enough to realize that what did happen wasn't in line with their perfectly reasonable expectations.
This post illustrates a situation where Tableau's Calculated Field evaluation results in values for the calculated field that are in some circumstances not what the User intended or expected.
In this scenario, there are a set of records containing Budget, Released, and Spent values for US and Canadian States and Provinces. The User is interested in determining how much of the budget is unspent, or surplus, which from his/her perspective is exactly what the common meaning of that statement is.
Arithmetically the relationship is Budget – Spent, and in the case where no money has been spent the unspent amount is the full Budget amount. As we see below, when the data is in an Excel spreadsheet and the formula {Budget} - {Spent} is evaluated the result is exactly as intended. However, the arithmetically equivalent formula in a Tableau Calculated Field will produces an erroneous, from the human perspective, result when there is no data for the relevant Spent field.
Tableau's great grace is its human-oriented design that lets the User do the thing most natural to their experience and expectation with the confidence that Tableau will do the right thing.
As this example shows, Tableau sometimes doesn't live up to this promise. But that can, and should, be remedied.
Chris, I don't understand what you are getting at? I know what the confusion is with using aggregated or non aggregated calculations is, and it is a blog post worth writing. However, your examples don't match what your text says. Also, the data is sorted differently in the tableau table, making it hard to compare. And as far as I can tell, the tableau examples match excel in every example in this post. Can you try and make things clearer? Also, is there an alternative approach you recommend tableau should take?
ReplyDeleteAndy: I think I've addressed your concerns, let me know if it's still unclear.
ReplyDeleteTableau handles Null as expected in both examples.
ReplyDeleteIf you change your formulas to:
ZN([Budget])-ZN([Spent])
and
ZN(SUM([Budget]))-ZN(SUM([Spent]))
or
SUM(ZN([Budget]))-SUM(ZN([Spent]))
maybe that would produce the results you are looking for. Null is something different from zero.
If you want to treat Null as zero, Tableau provides a ZN() function. If you think Tableau should should ZN() Null values by default, then I disagree with you.
I like how Tableau treats Null, it works exactly as expected, and I think Excel is the application that does the wrong thing.
Ay, Joe, there's the rub: the old "as expected." If one's familiar with the technical distinction between null and zero then there's a case to be made for Tableau's behavior being as expected.
ReplyDeleteBut for the great majority of ordinary run-of-the-mill people, the concept is that when I'm subtracting one thing-'Spent' from another-'Budget' to see how much is unspent and there's no record of anything being spent than the unspent amount must be the entire budget amount.
Those of use who've spent our professional lives in technical programming environments know the "null isn't zero so an expression that uses it must evaluate to null" rule, but that's an artifact of a technical view of the world in a product that's in most aspects designed for nontechnical people.
And even when the null-not-zero treatment holds, why isn't the total of the "Spent" columns in the Tableau tables above null? It should be, if when one member of a sum is null the result is null, or am I missing the special case that says otherwise?
Chris,
ReplyDeleteIn my experience, Tableau defaults to how SQL deals with null, and tries to be consistent with that logic. But I see your point, in some languages a sum with null will be null, it is something Tableau can change to make it easier for those who do not already know SQL, and how much pain would it be for someone who does know SQL.
So the question becomes, whose expectations do they build an application for?
You have so many great ideas, why not develop your own application? I would likely buy it :)
I think a big part of the challenge is Tableau's documentation needs work (as has been pointed out in other posts on Chris's blog). Somehow, common "gotcha's" like Null handling, that make things very challenging for non-technical users to wrap their brains around (and sometime trip-up experienced users), should be highlighted in Tableau's documentation, rather than buried in forums or third-party blogs. Clicking help for various functions should come up with more than the most simplistic explanation and obvious use case, or at least a link to additional examples, or to a forum/kb search for articles on the function.
ReplyDeletePersonally, I need to make note of the ZN() function and see how my workbooks could be simplified through its use. I have many Tableau workbooks that rely on database views built primarily to aggregate and/or fully-populate sparse datasets and force nulls to be converted to zeros before the data gets into Tableau.