Time Intelligence

john_cross · ‎06-01-2022

Introduction

A frequent request in the BI world is comparing a metric over time. Has this metric improved, worsened, or stayed the same in the last week, month, or year?

This article explains how Incorta handles these Time Intelligence scenarios.

What you need to know before reading this article

Date Dimension

Let's Go

Week/Month/Quarter/Year To Date

After joining a dataset to the Date Dimension, you can start creating flags to identify which records will be used for your analysis. These flags and metrics would ideally be placed in a Business Schema. This will help with reusability for other metrics and reduce duplication of logic.

YTD Flag:

if(
	and(
		DateSchema.DateTable.Date <= $currentDate, 
		date.date.Year = $currentYear
	), 
	1, 
	0
)

QTD Flag:

Add quarter(DateSchema.DateTable.Date) = $currentQuarter to the YTD And constraint

MTD Flag:

Add DateSchema.DateTable.Month = $currentMonth to the YTD And constraint

WTD Flag:

Add DateSchema.DateTable.Week = $currentWeek to the YTD And constraint

Once the desired Time Intelligence flags have been set up, you can create your metrics. For a YTD Revenue metric, create a New Formula: YTD_Flag * Revenue.

Date Over Date

This section assumes you are using the Date_US.csv that ships with Incorta. If you use a custom date dimension, ensure the below concepts are available.

After joining your dataset to the date dimension, you will need to create an alias of the table that holds the metric required for analysis. Join that table as the child, to the Date Dimension on the year_ago_date for a YoY metric. For other metrics, create an alias and join on the appropriate column that ends with _ago_date. You can choose from the prebuilt day, week, month, quarter, or year columns for your analysis.

Add the metric from the original table and the alias table into an insight. Make sure to include the appropriate date column from the Date Dimension.