
1. Re: Calculated field for sum of current year (CY) sales
Simon Runc Mar 5, 2016 8:06 AM (in response to Ryan Nixon)1 of 1 people found this helpfulhi Ryan,
This is probably a nobrainer 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 rowlevel and aggregates in a calculated field. Your [Year] = 2016 is a Row Level calculation (I like the term, offcanvas)....as a rowlevel 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.
So either
IIF([Year]=2016, [Sales],0)
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
SUM(IIF([Year]=2016, [Sales],0))
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

2. Re: Calculated field for sum of current year (CY) sales
Ryan Nixon Mar 5, 2016 10:18 AM (in response to Simon Runc)That works! Thanks Simon! And thank you for the explanation too!