top of page
Search
Ryan Forsythe

Additional Date forms to include in your Date Hierarchy

Updated: Jan 20, 2021


Additional considerations for your date hierarchy attributes for easier reporting.

In my experience, I have worked with over 40 companies and organizations. I consistently run into several elements that are missing, that when implemented, make it much easier to perform dynamic date reporting at levels higher than the Date level (Month, Quarter, Year).

The typical Month data has the following columns:

MonthID - 201701

MonthDesc - January, 2017

MonthNumber - 1

QuarterID - 20171

YearID - 2017

Adding in additional elements provides much more flexibility and easier use of the MicroStrategy platform.

Time period beginning and/or end date - When this is included as an additional attribute form of the Week/Month/Quarter/Year attribute, this permits using the Dynamic Date functionality.

Time period from current – This allows filtering or sorting based on a calculation of a date difference of the current date and a point in the time period. The current month would have a value of 0, the prior month would have value of 1, and next month would have a value of -1. Incrementing/decrementing from there.

Time period number of days – Provides the number of days within the time period. This may be used for fact extensions.

Time period prior period – Used for Transformations.

Time period prior year period – Used for Transformations.

These can very easily be included in a view, and for some things such as the Time Period from Current, should actually be calculated in a view. In a previous post, I detailed use cases for using views rather than directly using a table.

http://www.mastrarchitect.com/single-post/2016/1/26/The-Case-for-Database-Views

Example 1 Time period beginning date

Example 2 – Time period from current

Sample code for views:

Time period beginning and/or end date - When this is included as an additional attribute form of the Week/Month/Quarter/Year attribute, this permits using the Dynamic Date functionality.

SQL Server:

Start Date

CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(date_id)-1),date_id),101) AS month_start_date

case when date_id = '1900-01-01' then '1900-01-01' else DATEADD(DAY, 1 - DATEPART(WEEKDAY, date_id), CAST(date_id AS DATE)) end week_start_date

cast(DATEADD(q, DATEDIFF(q, 0, date_id), 0) as date) AS quarter_start_date

convert(date,cast(((datepart(yyyy,date_id) * 10000) + 101) as varchar(8)),112) AS year_start_date

End Date

case when date_id = '9999-12-31' then '9999-12-31' else DATEADD(DAY, 7 - DATEPART(WEEKDAY, date_id), CAST(date_id AS DATE)) end week_end_date

case when datepart(yyyy,date_id) = 9999 then '9999-12-31' else

CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,date_id))),DATEADD(mm,1,date_id)),101) end AS month_end_date

case when datepart(yyyy,date_id) = 9999 then '9999-12-31' else cast(DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, date_id) + 1, 0)) as date) end AS quarter_end_date

convert(date,cast(((datepart(yyyy,date_id) * 10000) + 1231) as varchar(8)),112) AS year_end_date

Time Period From Current

datediff(mm,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(date_id)-1),date_id),101),getdate()) AS month_from_current

datediff(q,cast(DATEADD(q, DATEDIFF(q, 0, date_id), 0) as date),getdate()) AS quarter_from_current

datepart(yyyy,getdate()) - datepart(yyyy,date_id) AS year_from_current

Time Period Number of Days

case when datepart(yyyy,date_id) = 9999 then 31 else datepart(d,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,date_id))),DATEADD(mm,1,date_id)),101)) end AS days_in_month

case when datepart(yyyy,date_id) = 9999 then 90 else datediff(d,cast(DATEADD(q, DATEDIFF(q, 0, date_id), 0) as date), cast(DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, date_id) + 1, 0)) as date)) + 1 end AS days_in_quarter

datepart(dy,convert(date,cast(((datepart(yyyy,date_id) * 10000) + 1231) as varchar(8)),112)) AS days_in_year

Time period prior period

(datepart(yyyy,dateadd(m,-1,date_id)) * 100) + datepart(mm,dateadd(m,-1,date_id)) prior_month_id

(datepart(yyyy,dateadd(q,-1,date_id)) * 10) + datepart(q,dateadd(q,-1,date_id)) AS prior_quarter_id

datepart (yyyy,date_id) -1 prior_year_id

Time period prior year period

(datepart(yyyy,dateadd(m,-12,date_id)) * 100) + datepart(mm,dateadd(m,-12,date_id)) prior_year_month_id

(datepart(yyyy,dateadd(m,-12,date_id)) * 10) + datepart(q,dateadd(m,-12,date_id)) AS prior_year_quarter_id

Oracle

Start Date

CASE WHEN TO_NUMBER (TO_CHAR (date_id, 'D')) = 7 THEN MYDATE

ELSE MYDATE - TO_NUMBER (TO_CHAR (date_id, 'D'))

END AS week_start_date

TRUNC (date_id, 'mm') AS month_start_date

TRUNC (date_id, 'Q') AS quarter_start_date

TRUNC (date_id, 'Y') AS year_start_date

End Date

CASE WHEN TO_NUMBER (TO_CHAR (date_id, 'D')) = 7 THEN date_id + 6

ELSE date_id + (6- (TO_NUMBER (TO_CHAR (date_id, 'D'))))

END AS week_end_date

LAST_DAY (TRUNC (date_id, 'mm')) + 1 - 1/86400 AS month_end_date

ADD_MONTHS (TRUNC (date_id, 'Q'), 3) - 1/86400 AS quarter_end_date

ADD_MONTHS (TRUNC (date_id, 'Y'), 12) - 1/86400 AS year_end_date

Time Period From Current

months_between(TRUNC (date_id, 'mm'), sysdate) month_from_current

months_between(TRUNC (date_id, 'Q'),TRUNC (sysdate, 'Q'))/3 quarter_from_current

TO_NUMBER(TO_CHAR (sysdate, 'yyyy')) - TO_NUMBER(TO_CHAR (date_id, 'yyyy'))

Time Period Number of Days

TO_NUMBER ( TO_CHAR( LAST_DAY (TRUNC (date_id, 'mm')), 'DD')) AS days_in_month

ADD_MONTHS (TRUNC (date_id, 'Q'), 3) - TRUNC (date_id, 'Q') AS days_in_quarter

ADD_MONTHS (TRUNC (date_id, 'Y'), 12) - TRUNC (date_id, 'Y') AS days_in_year

Time period prior period

TO_NUMBER((TO_CHAR (add_months(date_id,-1), 'yyyy') * 100) + TO_NUMBER (TO_CHAR (add_months(date_id,-1), 'MM'))) prior_month_id

TO_NUMBER((TO_CHAR (add_months(date_id,-3), 'yyyy') * 10) + TO_NUMBER (TO_CHAR (add_months(date_Id,-3), 'Q'))) prior_quarter_id

TO_NUMBER(TO_CHAR (date_id, 'yyyy')) - 1 prior_year_id

Time period prior year period

TO_NUMBER((TO_CHAR (add_months(date_id,-1), 'yyyy') * 100) + TO_NUMBER (TO_CHAR (date_id, 'MM'))) AS prior_year_month_id

TO_NUMBER((TO_CHAR (add_months(date_id,-12), 'yyyy') * 10) + TO_NUMBER (TO_CHAR (date_Id, 'Q'))) prior_year_quarter_id

- Ryan Forsythe, Course Creator, MaSTR Architect, Chief Analytics Officer, AllScient


80 views0 comments
bottom of page