All BI systems need to manipulate source system dates in order to produce derived columns such as Month or Year, which are useful in reports or dashboards.
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.
#Design Tip 1: It is sometime very useful to have a Date/Time stamp as a column in a datafeed to record the Date/Time the query was last run eg. SELECT GETDATE() AS DateTimeStamp FROM Tablename
#Design Tip 2: Having a Date/Time stamp column present in a datafeed allows Elapsed Time calculations to be performed in the dataset using the inbuilt datediff date function.
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