Handling Source System Dates

Follow

All BI systems manipulate source system dates in order to produce derived columns such as Month or Year, which are useful in reports or dashboards. For Intuitive, the manipulation is commonly performed within the datafeed from the source system, either within the extracting SQL statement or in calculated columns within the source spreadsheet.
  
The advantage of preparing descriptive information based on dates within the datafeed are:

  • The logic does not need to be understood by the user building components 
    and dashboards.
  • Performance of the system is improved since the calculation 
    of date-derived functions need not be performed each time a component is 
    refreshed.
  • More sophisticated derived calculations can be used for financial 
    year, academic year, financial week etc. Such derivations are highly bespoke to 
    each organisation and require the capability of SQL or Excel to process the 
    source information.

  
The following list describes some of the more popular functions and shows how they are implemented in both SQL Server and Excel. Other database engines and programming languages provide similar options - please refer to your documentation.
  
Year
SQL Server function: DATEPART(yyyy, MyDateColumn) AS Year
Excel function: =YEAR(MyDateCell)
Purpose: Allows information within a dataset to be aggregated up to year level within a component.
  
Years Elapsed
SQL Server function: DATEDIFF(yy, MyDateColumn, GETDATE()) AS YearsElapsed
Excel function: =DATEDIF(MyDateCell,TODAY(),"y")
Purpose: Allows a filter to be set within the dashboard limiting results to a certain range of calendar years relative to the current year. Common uses would be YearsElapsed=0 (for the current calendar year / year to date); YearsElapsed=1 for last calendar year; YearsElapsed<2 for the current and last calendar years.
  
Period
SQL Server function: CONVERT(varchar(7), MyDateColumn, 23) AS Period
Excel function: =CONCATENATE(TEXT(YEAR(MyDateCell),"0000"),"-",TEXT(MONTH(MyDateCell),"00"))
Purpose: Creates an x-axis attribute of the format eg 2014-07 (for July 2014).  Allows information within a dataset to be aggregated up to month level within a component. This should be the first choice of attributes to display on the x axis of a chart since it allows months to be displayed straddling multi-year boundaries. Also it provides assurance to the user that they are looking at one particular month within one year.
  
Periods Elapsed
SQL Server function: DATEDIFF(mm, MyDateColumn, GETDATE()) AS PeriodsElapsed
Excel function: =DATEDIF(MyDateCell,TODAY(),"m")
Purpose: Allows a filter to be set within the dashboard limiting results to the last n months. Common uses would be PeriodsElapsed = 0 (for current month); PeriodsElapsed = 1 (For last month); PeriodsElapsed BETWEEN 1 AND 12 for the last rolling 12 month period.

Month Number
SQL Server function: DATEPART(mm, MyDateColumn) AS MonthNumber
Excel function: =MONTH(MyDateCell)
Purpose: Allows a filter to be set within the dashboard having, say Monthnumber<10 or MonthNumber BETWEEN 4 AND 6 or MonthNumber = 4. Can also be used for x axis on a component that does not span multiple years.
    
Week Number
SQL Server function: DATEPART(ww, MyDateColumn) AS WeekNumber
Excel function: =WEEKNUM(MyDateCell)
Purpose: Displays 1-53 within the calendar year; useful as an x-axis value for organisations having weekly reporting, or as a filter if the dashboard is to show the past n weeks etc.

Year-Week
SQL Server function: CAST(DATEPART(yyyy, MyDateColumn ) AS char(4)) + '-' + RIGHT (CONVERT (varchar, 0) + CONVERT (VARCHAR, DATEPART(wk, MyDateColumn)), 2) AS [Year-Week]
Excel function: =CONCATENATE(TEXT(YEAR(B6),"0000"),"-",TEXT(WEEKNUM(B6),"00"))
Purpose: Creates an x-axis attribute of the format eg 2014-45 (for week 45 of 2014).  Allows information within a dataset to be aggregated up to week level within a component. This should be the first choice of attributes to display on the x axis of a chart since it allows weeks to be displayed straddling multi-year boundaries. Also it provides assurance to the user that they are looking at one particular week within one year.
  
Weeks Elapsed
SQL Server function: DATEDIFF(ww, MyDateColumn, GETDATE()) AS WeeksElapsed
Excel function: =ROUNDDOWN((TODAY()-MyDateCell)/7,0)
Purpose: Allows a filter to be set within the dashboard limiting results to the last n weeks. Common uses would be WeeksElapsed = 0 (for current week); PeriodsElapsed = 1 (For last week); WeeksElapsed BETWEEN 1 AND 52 for the last rolling 52-week period.
  
Date (no time component)
SQL Server function: DATEADD (dd, DATEDIFF (dd, 0, MyDateColumn), 0)
Excel function: =INT(MyDateCell)
Purpose: Removes the time component from a datetime type source column having both dates and times present. This allows consistent drilldown and aggregation of information within the dataset without the dashboard server considering two dates to be different values (since they each have a different time component).
  
Hour
SQL Server function: DATEPART(hh, MyDateColumn)
Excel function: =HOUR(MyDateCell)
Purpose: For source system datetime columns containing a time component, it may be useful to extract the hour for use in components eg as a drilldown beneath ‘number of calls by day’.

 

An example of an extract from SQL Server using the above functions:

SELECT

 Region, EventType, JobNumber, Status, Consultant, UserLeft, Client, JobType, JobTitle, Sector, Company, VacancyLocation, VacancyDetails, PositionType,  UserID, CompanyId,Office, Country, TeamName, TeamManager, VacancyID, InterviewId, CandidateId, CandidateName, InterviewNo, Placements, PlacementDiscipline, InvoiceNumber, FeeTotal, Positions,FeeSales, FeeWIP, FeeConvertedSales, MeetingID, Interview1_ID, Interview2_ID, Interview3OrMore_ID, CallsCount, CallsDuration

 
EventDate,

 DATEPART(yyyy, EventDate) AS EventYear, DATEPART(mm, EventDate) AS EventMonthnumber,

 CONVERT(varchar(7), EventDate, 23) AS [EventYear-Month],

 DATEDIFF(mm, EventDate, GETDATE()) AS EventMonthsElapsed,

 DATEDIFF(yy, EventDate, GETDATE()) AS EventYearsElapsed,

 DATEPART(ww, EventDate) AS EventWeeknumber,

 CAST(DATEPART(yyyy, EventDate ) AS char(4)) + '-' + RIGHT (CONVERT (varchar, 0) + CONVERT (VARCHAR, DATEPART(wk,EventDate)), 2) AS [EventYear-Week],

 DATEDIFF(ww, EventDate, GETDATE()) AS EventWeeksElapsed,

 FROM

 dbo.View_AllRegions_Union

Have more questions? Submit a request

Comments

Powered by Zendesk