Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
