Time Attributes and Time Series Measures

Built-In Time Features

Birst includes a pre-defined Time dimension that allows you to analyze data by date and higher granularity calendar items such as week, month, quarter, and year.

The Time dimension has many useful attributes such as Time.Date, Time.Day, Time.Week End Date, etc. These support reporting and filtering based on time. For example, Revenue by Year/Month:

In addition, when a source includes date and datetime columns that are configured as Analyze by Date, Birst automatically provides time series measures, such as Year Ago, Trailing 12 Months, Year To Date, etc., relative to each such date for each relevant measure in the same source.

Birst also provides the Load Date and time series measures for it. The Load Date is the date when the data was loaded into Birst.

The Time dimension is available by default to Birst warehouses. To add the Time dimension and enable time series analysis for a Live Access source see Adding Time Series to a Live Access Source.

If you need to add time attributes or period shifts that are specific to your organization see Creating a Custom Time Hierarchy.

Tip: The built-in Time attributes and Time Series Measures do not appear in the Admin UI. You can see them in the Subject Areas of Visualizer and Designer, and in the Dashboards 2.0 filter editor.

Time Range for Filters in a Space

The built-in Time dimension years range from 1900 to 2050. You can limit this range for filters (prompts) by going to Admin - Manage Space - Modify Properties and setting the Min/Max Years. The default range is specified as -1 (1900) and -1 (2050).

Best Practice: Change these values to match the time frame of the filtering requirements for the space. Restricting the time frame makes your filters more efficient and easier to develop. For example, if the source data for the space goes back only to the year 2000, you could set the minimum year to 2000, and you won't see previous years in the Time filters on a report.

Time Variables

The built-in Time dimension is useful for creating custom time variables that can dynamically filter reports or set default values for dashboard filters. Go to Admin - Customize Space - Variables to create custom time variables.

Time Attributes

The Time dimensional attributes are available for use in Visualizer, Designer, and Dashboards 2.0.

Time Attributes in Visualizer

The Visualizer Subject Area lists the Time dimension attributes.

Time Attributes in Designer

The Designer Subject Area lists the Time dimension attributes.

Time Attribute Filters in Dashboards 2.0

A dashboard filter can use the time attributes.

Time Series Measures

When the Analyze By Date feature is enabled for a date type column, Birst automatically creates time series measures for each measure in the hierarchy. This feature helps you report on a measure in multiple ways against time.

Tip: The built-in time series types can be extended to include YTD YAGO (Year to Date, Year ago), QTD YAGO (Quarter to Date, Year ago), MTD (Month to Date), MTD YAGO (Month to Date, Year ago), DAGO (Days Ago), WTD (Week to Date). These are not available by default for all spaces primarily to avoid an increase in the volume of the default subject area, especially if these extended time series do not add any value to your reporting needs. If you do need them, contact Support to have these enabled for your space.

Only enable Analyze by Date in order to ensure that all measures in your space can be queried using the Birst Time Dimension, according to the date values in that column. You do not need to check Analyze by Date if you are attempting to use that date column by itself (i.e., including the date as part of calculations such as date related transformations or ETL scripting).

Time Series Measures in Visualizer

Visualizer lists the Times Series Types in the properties for each measure added to a report.

Time Series Measures in Designer

Designer list the Times Series Measures in a special section of the Default Subject Area.

Time Series Measure Filters in Dashboards 2.0

Dashboards 2.0 supports the time series type for measures used as dashboard filters.

Examples of Using Time Series in Reports

A report that uses a time series measure requires that a lower granularity time attribute be part of the report, either as a column or as a filter or both.

For example, the Year to Date time series requires that a lower granularity time attribute, such as Time.Year/Quarter, Time.Year/Month, or Time.Week Start Date, is part of the report as either a column or a filter or both. Similarly, Trailing 3 Months requires an attribute such as Time.Year/Month, Time.Week Start Date, or Time.Day of Month be in the report.

Current and Year To Date Measure in Visualizer

The following report shows, at the year level, how Year to Date (YTD) sums up the YTD values for the four quarters in the year.

The report uses the following BQL, as shown in Advanced Tools - Queries:

SELECT TOP 100 USING OUTER JOIN [Order_Date: Sum: Gross_Margin] 'COL0' , [Products.Categories] 'COL1' , [Time.Year/Quarter] 'COL2' , [YTD Order_Date: Sum: Gross_Margin] 'COL3' FROM [ALL] WHERE ( ( [Time.Year/Quarter]='2014/Q1' OR [Time.Year/Quarter]='2014/Q2' OR [Time.Year/Quarter]='2014/Q3' OR [Time.Year/Quarter]='2014/Q4' ) ))

Current and Year Ago Measure in Visualizer

The report uses the following BQL, as shown in Advanced Tools - Queries:

SELECT USING OUTER JOIN [Order_Date: Sum: Revenue] 'COL0' , [Time.Year/Month] 'COL1' , [YAGO Order_Date: Sum: Revenue] 'COL2' FROM [ALL] WHERE ( ( [Time.Year/Month]>='2013/01' ) )

Time Syntax in BQL

Date Format Syntax

There are two syntax formats for Time.Date expressions, depending on how the date is formatted.

SELECT USING OUTER JOIN [Order_Date: Sum: Quantity] 'Quantity' , [Time.Date] 'Date' , [YTD Order_Date: Sum: Quantity] 'YTD Quantity' FROM [ALL] WHERE ( ( [Time.Date]>=#2014-04-01# AND [Time.Date]<=#2014-04-30# ) )

Or:

SELECT USING OUTER JOIN [Order_Date: Sum: Quantity] 'Quantity' , [Time.Date] 'Date' , [YTD Order_Date: Sum: Quantity] 'YTD Quantity' FROM [ALL] WHERE ( ( [Time.Date]>='4/1/2014' AND [Time.Date]<='4/30/2014' ) )

NOWDATE and NOW

You can use the Time attributes with the BQL NOWDATE to return the current date at midnight or NOW to return the current date and time.

SELECT USING OUTER JOIN [Order_Date: Sum: Quantity] 'Quantity' , [Time.Date] 'Date' , [YTD Order_Date: Sum: Quantity] 'YTD Quantity' FROM [ALL] WHERE ( ( [Time.Date]>= NOWDATE ) )

Tip: The Time dimension's resolution is at the day level. NOWDATE has an hour component of midnight, for example: 2014-08-12 12:00:00.