5 Replies Latest reply on Apr 24, 2017 3:46 AM by Simon Runc

    How to draw line for Same Period Last Year

    Krzysztof Bordzan



      I have stuck with one issue.

      Have tried to look for solution on Forum, but didn't manage.


      I have prepared sample worksheet on sample data. Similar to the worksheet, which I'm trying to create.

      Problem is: how to draw line presenting same period last year.


      Would it will require to use Parameters or Sets?

        • 1. Re: How to draw line for Same Period Last Year
          Simon Runc

          Hi Krzysztof,


          So there are a few ways to go about this. The simplest, and I think where you are getting stuck is that you want to use the DATEPART month, and not the DATETRUNC month.


          The DATEPART (month) is Jan, Feb, March... so allows us to plot (by bringing year onto colour) Jan for 2015 and 2016.


          In the attached I've used a filter to only return the last 2 years (this is just so it works dynamically, when we get to 2018)



          I've then used the DATEPART Month (I've marked the DATEPART options in Blue, and the DATETRUNC options in Green in the below image)



          Once we have this we can just drag Year onto colour so we get a line per year.


          Hope that makes sense.

          1 of 1 people found this helpful
          • 2. Re: How to draw line for Same Period Last Year
            Krzysztof Bordzan

            Hi Simon,


            Thank you for detailed explanation. I was able to replicate it by myself with provided details.


            One additional question:

            In current setup analysis will be static (not possible to select year). Is it possible to have in the same time possibility to select period, and still have two lines present on the graph?

            • 3. Re: How to draw line for Same Period Last Year
              Simon Runc

              Yes that's not a problem...again lots of ways to do this.


              So I've created a parameter with the Year to select [2013,2014,2015,2016]...and then created this calcuation


              [TY/LY Flag/Filter]

              IF [Order Date Year] = [Select Year] THEN 'TY'

              ELSEIF [Order Date Year] = [Select Year]-1 THEN 'LY'

              ELSE 'Hide'



              I then bring this onto the filtershelf and exclude 'Hide'


              I then also bring this onto the colour shelf (the reason I do this is if you set up the colours for TY/LY they will persist...if we use year and you set 2016 [TY] to dark grey, and 2015 [LY] light grey...if you change the parameter the colours are linked to the actual year...so 2014 would take a different colour. By the selected year always being TY, and the previous year always called 'LY' they will always be the colours we select for TY/LY)


              Hope that does the trick

              1 of 1 people found this helpful
              • 4. Re: How to draw line for Same Period Last Year
                Krzysztof Bordzan



                Your my hero again


                One additional thing:

                if year selected is not ended yet, then we have information about nulls:



                Is there any way to display same number of months for line presenting SPLY?


                And question about formatting: lines in your files looks much batter for visualization - thicker and with dot points. Where in settings I can change it?

                • 5. Re: How to draw line for Same Period Last Year
                  Simon Runc

                  Glad it helped!


                  So for the lines...we can change all that in the Colour Tile (I've marked the option to add 'dots')...I also use the Size tile to reduce the size of the line (this gives, IMHO, a nice thinner/sharper line)



                  On the YtD...


                  So there a few ways to do this, and we are going a little deeper in Tableau!


                  As the Sample Superstore data goes to Year end (December), I've used an extract filter, so the data now only goes to October...so we have a typical situation.


                  First thing I want is to pick up the last date of the data (I've used an LoD for this)


                  [Last Date of Data]

                  {MAX([Order Date])}


                  btw this is just shorthand for {FIXED: MAX([Order Date])}...we can loose the FIXED if it's dimensionless


                  Next I want to use this so the data only goes back to October (for both years)


                  [Filter to YtD]

                  DATEPART('month',[Order Date]) <= DATEPART('month',[Last Date of Data])


                  Notice the use of DATEPART...this says only include months that are <=10 (for any year)


                  The final bit, is that we only want this rule to be live if the user has selected the last year...so i add this formula


                  [Only Filter if Last Year Selected]

                  IF [Select Year] = YEAR([Last Date of Data])

                  THEN IIF([Filter to YtD],1,0) ELSE 1 END


                  I then bring this onto the filter shelf and set to 1...so now if we choose the last year, we only get data (for both years to October), else we get full years.


                  Hope that makes sense?