Preview the new community

We’re making it easier for you to find answers and network with others. Visit our preview site and let us know what you think.

Additional Date Forms to Include in Your Date Hierarchy

by Trusted Expert Trusted Expert on ‎01-31-2017 02:14 PM - last edited on ‎02-02-2017 04:03 PM by Community Manager (989 Views)

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:

https://community.microstrategy.com/t5/Business-Strategy-Best-Practices/The-Case-for-Database-Views/m-p/282107/highlight/true#M3218

 

 

Example 1 - Time Period Beginning Date

 pic1.png

 

Example 2 – Time Period from Current

 pic2.png

 

Sample code for views:

 

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

Comments
by Trusted Guru Trusted Guru
on ‎04-03-2017 03:38 PM

This is wonderful work Ryan!!

by Santosh Kumar P Junior Contributor
on ‎04-20-2017 11:32 AM

Thank you very much Ryan for sharing this.