Search

Dynamically change Time Series Types in Reports

Overview

Birst automatically provides time series analysis with the built-in Time dimension.

Your organization may have your own specific time attributes and time period shifts. In this case you may need to define a custom set of time attributes. You can add your own custom time hierarchy definition to a Birst data warehouse.

This tutorial walks you through how to create custom time categories, how to add them to your reports and how to include them in dashboard filters.

Components

Analytics

Requirements

  • The right to Edit Dashboards, use Visualizer, Saved Expressions.
  • A properly modeled dataset, specifically that the Measure comes from a fact table with a date, and that the date is connected to the Time dimensions, in other words, that the ‘Analyze By Date’ is toggled on.
  • If you are using an Infor Cloudsuite, you can find the corresponding IFS roles in the CloudSuite Analytics documentation. For example, in M3 Analytics you need the ‘M3A Administration‘ role for Admin and Visualizer access.

Tutorial

Difficulty: Easy
Estimated Completion Time: 10 minutes

Infor Birst has a great ability to quickly compare data between different periods. All that’s needed is a measure, a date, and you can choose one of the following Time Series Types:

When building reports, there is no way to create additional custom time categories, like Trailing Half Year, Trailing Four Months, or any Weekly Time Series.

Also there could be customers that need to analyze data weekly, and while access to extra time categories can be enabled through support there is a way to develop these and more time categories in Visualizer.

In situations like this we can use the power of the function RSUM and build a report that will show data based on a Time Series we define.

In addition to time categories RSUM can calculate the trailing sum across any dimension, including text such as for example roman numerals I, II, III, IV.

1. Using RSUM to calculate the time category

For the Trailing N Months, we can simply use Running Sum or RSUM.

Birst has a built in Trailing 3 Months which we can replicate with a function: RSUM(window size,[ Measure]) where window size is the number of periods to consider, be it weeks, days or in this case, three months.

Using the BQL Editor, we can create expressions.

SavedExpression(‘Custom T3M’) = RSUM(3,[OrderDate: # Distinct OrderID]). We can easily change this to be Trailing 4, 5, 6 or any number of periods by changing the window size.

2. Display Filtering and making sure all the data you need is included in the report

You can see in the above image, that 01/2021 column only includes the data for that month.

This is controlled by the Filter/more options and can be changed.

When ‘Use as a display filter’ is toggled off only the displayed data is used in the calculation.

When ‘Use as a display filter’ is toggled on all data is used in the calculation, and then only the relevant data is displayed.

Below is an image of the same report with ‘Use as a display filter’ toggled on.

Note that the same RSUM(3,[Measure]) will show three months data if the Date Dimension is monthly, it will show three days data if the Date Dimension is daily, and it will show three years data if the Date Dimension is yearly.

Furthermore, RSUM has no sense of time. So depending on the sorting used in the report, for January it can add Nov, Dec, and Jan when the months are sorted in descending order, but it will add Mar, Feb, Jan, if the order is ascending.

3. Non-standard time categories

While Infor Birst already offers Month Ago, with RSUM we can achieve 2 months ago or  5 months ago.

The measurement for Month Ago is RSUM(2,[Measure]) – [Measure].

The measurement for 2 Months Ago is  RSUM(3,[Measure]) – RSUM(2,[Measure]).

The measurement for Quarter Ago is RSUM(4,[Measure]) – RSUM(3,[Measure]).

The measurement for Year Ago is RSUM(13,[Measure]) – RSUM(12,[Measure]).