2 Replies Latest reply on Sep 23, 2018 3:48 AM by September Clementin

    Current Year vs Previous Year data for each month separately - how to create stacked graph per month

    Andrew Brew

      Hello Guys,

       

      I have encountered the following case regarding multiple measure for stacked bars.

      I have data (attached .xlsx) for each month for current and previous year, where CY 01 - Current Year data for January, PY 02 - Previous Year data for February and so on...

      I'd like to create visualisation that shows stacked bars for each month of the year, where I compare current year data for month to previous year data for the same month.

      So all together there should be 12 stacked bars, one for each month.

       

      Enclosed also raw .twbx which I'd like to modify to have stacked bars for January, February, ..., December comparing this data. Where months are on horizontal (X-axis).

       

      It would be great if there is anyone who can help me with this issue.

       

      Great Thanks in advance

       

      PS. Tableau Desktop Version: 10.3.3 (64 bit)

        • 1. Re: Current Year vs Previous Year data for each month separately - how to create stacked graph per month
          September Clementin

          Hi Andrew,

           

          As your data is structures such that you have a column for each month of measure data you will need to pivot it to get the desired result (or change the structure in the data source).  Here are the instructions to pivot your data in Tableau Desktop:

           

          • Clear your existing worksheet
          • Select 'CY01' and shift-click 'PY 12' (to select all measures).
          • Right-click > Hover over 'Transform' > Select 'Pivot'
          • This will leave you with 1 dimension (Pivot Field Names) and 1 measure (Pivot Field Values)
          • Right-click 'Pivot Field Names' > Hover over 'Transform' > Select 'Split'
          • Drag 'Pivot Field Names - Split 2' to columns
          • Drag 'Pivot Field Values' (measure) to rows
          • Drag 'Pivot Field Named - Split 1' to Color

           

          Hope this helps,

          September

           

          If this posts assists in resolving the question, please mark it 'helpful' and/or 'correct answer'. This will help other users find the same answer/resolution.  Thank you!

          1 of 1 people found this helpful
          • 2. Re: Current Year vs Previous Year data for each month separately - how to create stacked graph per month
            Don Wise

            Hello Andrew,

            Your data needed a bit of change in order to accomplish what you're looking to complete.  Tableau won't recognize CY vs PY labeling as 2017 vs 2018.

             

            Your underlying data therefore needed to be changed accordingly so that CY-01 = January 2018, CY-02 = February 2018 and correspondingly PY-01 = January 2017, PY-02 = February-2017.

            Screen Shot 2018-09-22 at 3.48.05 PM.png

             

            Thereafter, due to the way the data is shaped/structured, it additionally needed to be pivoted, selecting all columns (right-click) and then select Pivot:

            Screen Shot 2018-09-22 at 3.49.54 PM.png

            Which results in the following changes to the view:

            Screen Shot 2018-09-22 at 3.50.00 PM.png

             

            Field Names were then both changed/recast to be a date field (click on the abc icon and then select Date) and further renamed to Dates; (right-click) and select Rename:

            Screen Shot 2018-09-22 at 3.50.12 PM.png

            After that it was simply dragging and dropping the fields into the following order to get to this view:

            Screen Shot 2018-09-22 at 3.52.54 PM.png

            Hopefully it's what you're looking to do?  If yes, then please close this thread by marking this response as correct.  2018.2 workbook version attached.  Thx, Don

            2 of 2 people found this helpful