1 Reply Latest reply on Nov 21, 2016 5:07 PM by Patrick A Van Der Hyde

    Calculation field to compare values for a given month from current year to previous year

    tab dev

      Hi Everyone,

       

      I am trying to write a calculation field to compare a combination of values for a given month from current year to previous year. The calculation field I am trying to write should do the following comparison-

      1) For a given month in this year, if the sum of sales of 'Chairs' under furniture is greater than the sum of sales for the 'Chairs' in the previous year  then

      2) For a given month in this year,  if the sum of sales of 'binders' under office supplies is greater than the sum of sales for the 'binders' in the previous year then

      3) If Both 1 and 2 are true then display true

      4) If Either 1 or 2 or both 1 and 2 are false then display false.

       

      Please note as I a do not have the data for 2016, please consider current year as 2014 and the previous year as 2013 in the workbook.

       

      I would really appreciate any help with this.

       

      Regards,

      A fellow tableau learner!!

        • 1. Re: Calculation field to compare values for a given month from current year to previous year
          Patrick A Van Der Hyde

          Hello tab dev,

           

          Please remember to add the version number for your attached .twbx file when posting to the community.  Thank you so much for providing the sample data.  This is version 9.3 for anyone else that may open to assist. 

           

          I have attached one method of providing the answer to this question.  Note that you will need to have all the months/years in the view that are being compared to do this.  In the "Final" view, I have 'hidden' the first year of data since all of the potential values will always be False. 

           

          The view utilizes two different sets of table calculation to determine the comparisons.  The first is the lookup(sum([sales]),-12) which uses "Table Across" to look backwards 12 months for the Sales value.  

          The second table calculation use "Table Down" to sum together the '1' values for 'true' conditions for Binders and Chairs.  If both are summed to be equal to 2 then a True condition appears.

           

          Sheet 4 shows the final view based on the request.  Note that I have also hidden the values for 2011 (right click on 2012 header and click - 'show hidden data' if you want this to appear). 

           

          I hope this helps.

           

          Patrick