Time Functions with Date Intelligence

What is Date Intelligence?

Date Intelligence in Vivid is not a single feature, but rather a suite of related features. To get the most out of the BI data, analysts need to be able to query their cubes to obtain the dataset from specific points in time. Relevant time periods are highly variable between industries and ever-changing within companies. Our Time Functions are designed to make date ranges easier to work with.

The Time Functions

Vivid’s Time Functions make it simple for users to create Excel calculations that perform sophisticated date/time manipulation. To access the Time Functions, simply right click the name of the measure from the pivot table overlay that you wish to analyze over a period, and then select “Time Functions:”. From there, select the Time Function you’d like to invoke: Periods to Date, Parallel Periods, or Rolling Results. The final result is a new calculation that can be placed in the pivot table. These are native Excel calculations, fully functional for your team members who don’t yet use Vivid. Additionally, these calculations can be used in the formula of other custom calculations you create with Vivid.

Periods to Date

For a given measure, Periods to Date will create a calculation that will compute a roll-up for the specified period to the located date in the pivot table. Here is a pivot table that has a measure (Internet Order Count) and a calculation which is the Periods to Date version of this measure. The period used for this example is Fiscal Quarter:

From the screenshot above we can observe the following:

We see blanks for Parallel Inter net Order Count for two distinct reasons: - Since we’re lagging one quarter, periods that are of larger granularity don’t map to lagging by a quarter. For example, there is simple notion of a Fiscal Year that lags the current Fiscal Year by a quarter. - Q1 FY 2002 is blank and this is because there is no data prior to that quarter in the cube. - Regardless of which row you’re looking at, the Parallel Periods calculation is always lagged by one quarter. It doesn’t change on a row by row basis.

The underlying MDX created for this query looks like this:

([Measures].[Internet Order Count], ParallelPeriod([Date].[Fiscal].[Fiscal Quarter], 1, [Date].[Fiscal].CurrentMember))

Rolling Result

Rolling results will compute the rolling aggregation for a specified measure. Rolling results asks you to specify which hierarchy to roll against, most commonly a hierarchy from a time dimension. Additionally you specify the number of periods to roll-up. Like Periods To Date, you can also specify your aggregation operator. In the below example we use the SUM aggregation and set the Rolling Result:

Notice that Q1 FY 2002 is the same for both Internet Order Count and RR Internet Order Count, that’s because there’s no previous quarter to sum in the roll-up. - The rolling calculations is set to roll the past two periods for whichever level they’re at. Here are some examples: - Q2 FY 2002 is the sum of 565 and 448, the current and previous quarter. - 2003 is the sum of 3222 and 2206, the current and previous years. - August 3, 2002 is the sum of 6 and 8, the current and previous days. - The underlying MDX created for this query looks like this:

Sum(LastPeriods(2, [Date].[Fiscal].CurrentMember), [Measures].[Internet Order Count])

Editing Calculations

While the Vivid UI can create these custom Date Intelligence calculations, you can also tweak these calculations using MDX. Simply right-click the calculation in the Pivot Table Editor and select “Edit…” from the context menu. In the Edit Calculations dialog you can modify the calculation to do exactly what you want.

Feedback

Please feel free to give us feedback on our forums regarding your use of these Time Functions, as well as suggestions for any additional Time Functions that you’d find useful.

Kang Su Gatlin