10 Replies Latest reply on Jun 7, 2017 6:52 AM by Simon Runc

# How to draw line for Same Period Last Year

Hi,

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

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

Hi Simon,

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

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

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'

END

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

Great!

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

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?

• ###### 6. Re: How to draw line for Same Period Last Year

Hi Simon,

I have tried to recreate YtD filter for Fiscal Calendar.

Problem is that data for previous fiscal year are not filtered till december, but full year is taken.

I have tried to update formula for Last date:

{MAX([Order Date])} to use Fiscal Order Date instead, but then Filter YTD and Filter if LY are returning error about String,integer used instead of date.

I can't convert defualt because then filter CY/LY will stop working.

What I'm doing wrong?

• ###### 7. Re: How to draw line for Same Period Last Year

hi Krzysztof,

So I think all you needed to do here is use the Financial Year in the YtD Filter, and add the YtD filter to the filter shelf (and set to true)...

I've changed the YtD Filter to;

<=

I've then added this to the filter shelf and set to true...you can also verify this in the "data check" tab.

Hope that makes sense.

• ###### 8. Re: How to draw line for Same Period Last Year

Hi,

I haven't noticed that "magic" is in Filter YTD and that formula there is comparing two periods, not selecting one.

And that it should use DATEADD to select fiscal period.

Thank you!

• ###### 9. Re: How to draw line for Same Period Last Year

Hi Simon,

Looks like Fiscal Year is more tricky then we know.

I have noticed, that doesn't matter which Fiscal year you will select (also from past) results are filtered to max date which is December.

• ###### 10. Re: How to draw line for Same Period Last Year

Yes these financial years are pretty confusing!

I've changed the [Last Date]  field to take into account the selected Fin Year...

[Last Date - Selected Fin Year]

{MAX(IIF([Fiscal Year (Order_date)] = [Select Fiscal Year],[Order Date],NULL))}

before, as you say, we just had it as a Global Last Date.