Time intelligence: Custom calculations for greater perspective

This is the fourth blog in the series on how Pyramid brings time intelligence into analytics.

My previous posts on time-intelligence covered Pyramid’s powerful and automatic date-time calculation creation capabilities. We saw how users can create custom calculations over a range of dates, and then compare results with other periods. We also saw how Pyramid provides one-click wizards and menus to create effortless date-time calculations. Collectively, these tools provide a comprehensive, ready-to-go solution for non-technical users facing common analytical problems.

Despite the utility of Pyramid’s built-in time intelligence capabilities, users often need to create custom date-time calculations. In most BI tools, if the user has specific needs, it is almost impossible to achieve the exact result.

Pyramid provides two foundational tools for creating custom calculations: “lists” and “formulas.” Using these two capabilities we can build and add any type of date-time logic needed for analytics.

In this post, I will provide an example and outline of these tools and show how they can be used in date-time calculations. While these are advanced capabilities, they demonstrate how complex analytical logic can be implemented with Pyramid.

Date-time formulation tools

A general approach to date-time formulation requires the definition of three elements: a group of items on which to perform the calculation; a date that defines the grouping of the items; and a measure on which to perform the calculation.

A classic example of this is month-to-date sales, which we will illustrate below. In our example, the group of items includes all transactions that occurred during the month. The date is an input used to define the day in the month which will be used to define when the transactions occurred. And the sales amount is the measure used to quantify the transactions.

Custom lists

You can create dynamic lists (or “sets”) with Pyramid’s graphic list designer. For example, we can use the Month to Date (MTD) function to create a group of items that are all dates in the month up to the selected date. We can further enhance the dynamic MTD calculation by using a date parameter—which will allow us to dynamically pick the end date for that range—and change the dates used in the MTD logic. (“Parameters” are simply a predefined list of items that can be injected into another function.)

Custom formulas

The formula tool is used to create calculated values (as opposed to lists of items). Pyramid’s Formulate module has a graphic drop-and-drag user interface that enables you to build custom date-time formulas. For example, a user can sum all expenses incurred during the current year by creating a formula that automatically calculates the year-to-date (YTD) total for different metrics in the data set.

Business case example

Paul, a data analyst at Intensive Health Care, has produced a report that compares current month sales metrics with other period metrics. In addition, his CFO has requested a view of these figures from two years ago. This calculation must appear in the same report and all analyzed data should include this calculation.

Paul’s current report looks like this: he has MTD, WTD, QTD, and YTD calculations that are driven by a date parameter—currently set to November 15, 2019. This effectively calculates all sales in November through the 15th; then all sales in the quarter through to November 15th.

Now he needs to duplicate his base YTD calculation (created with the Formulate tool above), rename it as “YTD Previous -2,” and use the “Add Years” function to subtract two years, as seen in the highlighted text below. (Using a negative number with “Add Years” is effectively subtracting those years).

Adding this calculated member will result in an additional “YTD Prev -2” column, as shown in the table below. This column now displays YTD figures for January 1, 2017 through to November 15, 2017 (pivoting off the selection of November 15, 2019). More impressively, it does it for all metrics in the report, not just Sales.

Key takeaways

As you can see, Pyramid provides powerful tools to create custom lists and formulations that comprehensively extend basic time-date calculations. These advanced calculations let users examine data from non-traditional perspectives. In my last post in the time intelligence series, I’ll cover how Pyramid offers incredible control over selecting date ranges.