2 Replies Latest reply on Mar 5, 2016 10:18 AM by Ryan Nixon

    Calculated field for sum of current year (CY) sales

    Ryan Nixon

      Hi all,


      This is probably a no-brainer for many of you, but I cannot seem to figure out how to create a calculated field that sums only the sales for a specific dimension (year).  In this case, I am looking to sum the sales in the fiscal year 2016.  I cannot use the YEAR([Date]) function in lieu of my [Year] = 2016 calculation because I am on a fiscal calendar.


      I have also tried to convert the dimension to a measure and I get the same error.  This error can be seen below:



      Ultimately I'd like to have a YOY calculation and I cannot use this as a Table calculation - I need a field calculation.


      Please tell me this can be done.

        • 1. Re: Calculated field for sum of current year (CY) sales
          Simon Runc

          hi Ryan,


          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.


          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

          1 of 1 people found this helpful
          • 2. Re: Calculated field for sum of current year (CY) sales
            Ryan Nixon

            That works!  Thanks Simon!  And thank you for the explanation too!