Line Chart - Month Display

[#1]

In my year over year analysis i am using Month as my dimension.

I am having trouble with displaying month in a chronicle order. I tried every possible Sort combination but non of the options work for me.

If it all helps, here is how we are building our calendar:

timeline_raw:

SELECT time_period, fiscal_quarter, month_offset, month_name, month_start_date, Fiscal_YTD, QTD, MTD, YTD, RY, [Last 13 Months]

FROM (

SELECT

CAST(year as VARCHAR(4)) + '-' + CAST(month as VARCHAR(2)) AS time_Period,

'FY' + CAST(fiscal_year as VARCHAR(4)) + '.Q' + CAST(fiscal_quarter as VARCHAR(2)) AS fiscal_quarter,

DATEDIFF(month, getdate(), entry_date) as month_offset,

DATENAME(month,entry_date) as month_name,

month_start_date,

CASE WHEN entry_date>=(select max([entry_date]) from griffin.grf.timeline (nolock) where [entry_date]<=dateadd(d,0,datediff(d,0,getdate()))-1 and [Month]=7 and [Day]=1)

and    entry_date<=dateadd(d,0,datediff(d,0,getdate()))-1

THEN 'Fiscal_YTD' END AS Fiscal_YTD,

CASE WHEN Quarter=datepart(Q,getdate()-1)

and Year=Year(getdate()-1)

and    entry_Date<=dateadd(d,0,datediff(d,0,getdate()))-1

THEN 'QTD' END as QTD,

CASE WHEN left(convert(varchar,entry_date,112),6)=left(convert(varchar,getdate()-1,112),6)

and entry_date<=dateadd(d,0,datediff(d,0,getdate()))-1

THEN 'MTD' END as MTD,

CASE WHEN year(entry_date)=year(dateadd(d,0,datediff(d,0,getdate()))-1)

and entry_date<=dateadd(d,0,datediff(d,0,getdate()))-1

THEN 'YTD' END as YTD,

CASE WHEN entry_date between dateadd(m,-12,dateadd(d,0,datediff(d,0,getdate()))) and dateadd(d,0,datediff(d,0,getdate()))-1 THEN 'RY' END as RY,

CASE WHEN entry_date between

       dateadd(m,-13,dateadd(d,0,datediff(d,0,getdate()-day(getdate()))))+1

and dateadd(d,0,datediff(d,0,getdate()-day(getdate()))) THEN 'Last 13 Months' END as [Last 13 Months]

FROM grf.Timeline (NOLOCK)

) a

GROUP BY time_Period, fiscal_quarter, month_offset, month_name, month_start_date, Fiscal_YTD, QTD, MTD, YTD, RY, [Last 13 Months];

timeline:

LOAD time_period, fiscal_quarter, month_offset, month_name, month_start_date, Fiscal_YTD as Period

RESIDENT timeline_raw;

CONCATENATE(timeline) LOAD

time_period, fiscal_quarter, month_offset, month_name, month_start_date, QTD as Period

RESIDENT timeline_raw;

CONCATENATE(timeline) LOAD

time_period, fiscal_quarter, month_offset, month_name, month_start_date, MTD as Period

RESIDENT timeline_raw;

CONCATENATE(timeline) LOAD

time_period, fiscal_quarter, month_offset, month_name, month_start_date, QTD as Period

RESIDENT timeline_raw;

CONCATENATE(timeline) LOAD

time_period, fiscal_quarter, month_offset, month_name, month_start_date, MTD as Period

RESIDENT timeline_raw;

CONCATENATE(timeline) LOAD

time_period, fiscal_quarter, month_offset, month_name, month_start_date, YTD as Period

RESIDENT timeline_raw;

CONCATENATE(timeline) LOAD

time_period, fiscal_quarter, month_offset, month_name, month_start_date, RY as Period

RESIDENT timeline_raw;

CONCATENATE(timeline) LOAD

time_period, fiscal_quarter, month_offset, month_name, month_start_date, [Last 13 Months] as Period

RESIDENT timeline_raw;

DROP TABLE timeline_raw;

[prieper]

Script above does not show the formatting of the date-fields.

In general dates must be numeric (or dual value),

Axis then to be sorted by number, may also be changed to "continuous"

HTH

Peter