Dynamically change time series types in reports
Beginner | 10 Minutes
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.
📋 Requirements
|
Tutorial
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]).
On this page
- Dynamically change time series types in reports