1 Reply Latest reply: Dec 2, 2014 4:00 PM by Peter Rieper RSS

    Line Chart - Month Display

      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.

       

      12-2-2014 1-45-00 PM.png

       

      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;