1 of 1 people found this helpful
This is probably a no-brainer for many of you
Not at all...this is actually a good question, as it gets to the heart of Aggregate and Row Level calculations.
So the problem you are having is that you are trying to MIX row-level and aggregates in a calculated field. Your [Year] = 2016 is a Row Level calculation (I like the term, off-canvas)....as a row-level calculation, it is run over every row of the data (row by row), and the result is irrelevant of whatever the VizLoD (Viz Level of Detail). Where as SUM([Sales]) is an aggregate, as it is wrapped in the SUM...the result of this calculation is dependent on the VizLoD...so if you just brought SUM([Sales]) into your Viz (canvas) it would just be the total sales for everything, if you now brought in Region (thinking Superstore data) you would now have 3 numbers the SUM([Sales]) for each Region. so as you can see the result changes depending on your VizLoD...this is why you can't mix the 2. In your, above, formula Tableau knows that it needs to check the Year of each row, to see if it's 2016...but it then has to return SUM([Sales]), **** it doesn't know what SUM([Sales])! - hope that makes sense?
So for your question, you can just run the is Year=2016 over every row, and return the value if true, else 0 otherwise. We can then SUM this new column, and as the zeros won't add to the total we now have our year 2016 field.
and then when you use this in your Viz, it will be the SUM of this...or we can wrap the SUM in the question in one go
I prefer the 1st option, as I can AVG, MIN...etc. whereas the second one we are fixing the aggregate type as SUM.
Hope that helps and makes sense
That works! Thanks Simon! And thank you for the explanation too!