9 Replies Latest reply on Oct 30, 2015 6:08 AM by Ian Burdin

# YTD line chart comparison YOY

Looking to run a very simple line chart showing Jan-Oct 2014 (Year 1) traffic vs Jan-Oct 2015 (Year 2)

So obviously there is a 3 month gap with no data between Year 1 and Year 2, from Oct 15 to Jan 15, the start of Year 2

But when I run this in a continuous line chart, this missing data is shown as a line...when I use discrete dates obviously the common months are merged.

How do I split the line chart showing 2014 and 2015 as 2 lines?

Thanks

• ###### 1. Re: YTD line chart comparison YOY

Hi Ian

This is the way I do it...(using Superstore workbook)

First create a parameter called Reference Date, make it a date

Next create a calculated field - called LY YTD Flag

YEAR([Order Date]) == YEAR([Reference Date])-1 AND (MONTH([Order Date]) < MONTH([Reference Date]) OR (MONTH([Order Date]) == MONTH([Reference Date]) AND DAY([Order Date]) <= DAY([Reference Date])))

Now to identify the YTD sales and the equivalent from last year, create 2 new calculated fields

YTD

IF [Order Date] <= [Reference Date] AND DATEDIFF('year',[Order Date],[Reference Date])= 0 THEN [Sales] END

YTD (LY)

IF [LY YTD Flag] THEN [Sales] ELSE NULL END

Now you have all the items you need to build the chart - this is what it will look like

Right click mouse, select order date - drag to column, select month

drag YTD sales to row, drag YTD (LY) sales to row

right click YTD (LY) tick dual axis

add the parameter - set the date to somewhere in 2014 or before

Done

Working version attached 9.1.1

Cheers

Mark

1 of 1 people found this helpful
• ###### 2. Re: YTD line chart comparison YOY

You might also be interesting in using or at least knowing about following methods:

YOY YTD Bar Chart

Tinkering with Johan’s Unified Dates « Tableau Tinkering

1 of 1 people found this helpful
• ###### 4. Re: YTD line chart comparison YOY

brilliant many thanks Mark!

Im now trying to add in the YOY values but to no avail...workbook attached

On 30 October 2015 at 10:30, Mark Fraser <

• ###### 5. Re: YTD line chart comparison YOY

You're welcome Ian, hope it was clear to follow.

You may like to look at Kettan's message for an interesting alternative...

Cheers

Mark

• ###### 6. Re: YTD line chart comparison YOY

hi Ian

Im now trying to add in the YOY values but to no avail...workbook attached

Sorry, so you want the YoY difference displayed?

Like this?

Or something else?

• ###### 7. Re: YTD line chart comparison YOY

I've attached the workbook and the YOY calcs are incorrect...but I want to keep the format as per sheet 3

• ###### 8. Re: YTD line chart comparison YOY

Hi Ian

This is how I would calculate % change between 2 years...

(SUM([2015])-SUM([2014]))/SUM([2014])

So February

2014 = 798,096

2015 = 812,242

812,242-798,096 = 14146

14,146/798,096 = 1.77%

Feb 2015 is 1.8% higher than 2014.

in the attached I have replicated Sheet3 - but with the above methodology.

Cheers

Mark

• ###### 9. Re: YTD line chart comparison YOY

thats great thank you

On 30 October 2015 at 12:19, Mark Fraser <