Time Series

This information applies to both workbooks and classic dashboards.

Times Series Functions or Filter Functions?

Times series functions Ago and Todate offer a simple way to create metrics to calculate year-ago, year-to-date, and so on, and this method works well for business user reports. However, these functions generate complex queries that have a significant cost on performance. Also, each time that one of these functions is used in a report, an additional sub-query is generated using even more resources on the database.

In classic dashboards, instead of using time series functions, you can usually achieve the same result by using base metrics with the Filter function to filter on the appropriate time period. This method might be too complex for business user reports but it works great in dashboards pre-defined by the IT team. Using this method, the physical SQL generated is much simpler and more efficient, and doesn't require an additional sub-query. This means the SQL query will be faster and use less resources on the database, reducing the overall database CPU usage and IOs.

Here’s an example of the physical query that’s generated when you use both base metric and month Ago metric in the same report. Two queries are generated:

Description of the illustration ceal_physical_ago_query.jpg

Here’s the SQL generated when you use a Filter function instead. A single query is generated, and it's much simpler:

Description of the illustration ceal_filter_function_sql.jpg

How to Implement this in a Dashboard with Prompts

In most implementations, the dashboard already includes a prompt so users can select the month that they’re looking for. The first step is to identify the time periods that we need to filter on based on a user’s selection.

In this simple example, we assume that the calendar dimension includes a column Julian Period Number because it makes the calculation much easier. This is also feasible without Julian Period Number but would require much more complex formulas to calculate the time period selected.

First, the prompt for Month must fill a presentation variable (MonthSelected in the example below). Next, we create a separate dashboard prompt to retrieve the corresponding Julian Period Number and put it in another variable (PeriodNumberSelected). This second prompt isn’t shown to end-user, instead it’s hidden in the dashboard and the value calculated automatically based on variable MonthSelected. See the example below.

1. Modify the Month prompt to add the presentation variable.

   
   Description of the illustration ceal_month_prompt.jpg

2. Create a separate dashboard prompt for Julian Period Number.

   
   Description of the illustration ceal_julian_period_number_prompt.jpg

3. Edit your dashboard properties and click Filters and Variables.

   
   Description of the illustration ceal_dashboard_properties.jpg

4. Add the Julian Period prompt.

   
   Description of the illustration ceal_dashboard_filters_variables.jpg

5. Next, in the analysis, delete the global filter on the Month column, and use the column formulas filter functions based on Julian Period Number as required. Here are some examples:

   Current Month: Filter("Revenue Metrics"."Revenue" using "Time"."Julian Month Number"=@{PeriodNumberSelected}{80800})

   Month Ago: Filter("Revenue Metrics"."Revenue" using "Time"."Julian Month Number"=@{PeriodNumberSelected}{80800}-1)

   Year Ago: Filter("Revenue Metrics"."Revenue" using "Time"."Julian Month Number"=@{PeriodNumberSelected}{80800}-12)

   Year to date: Filter("Revenue Metrics"."Revenue" using "Time"."Julian Month Number"<=@{PeriodNumberSelected}{80800} and “Time”.”Year”=@{YearSelected}{2019})

How to Implement this in a Workbook with Parameters

The same principle can be applied in a workbook. Prompts and presentation variables are replaced by a dashboard filter and parameters.

Create three parameters: MonthSelected, PeriodNumberSelected, and YearSelected. Only the parameter MonthSelected is displayed in the canvas in a dashboard filter.

Parameter MonthSelected possible values are defined based on a logical SQL selecting all months.

For parameters PeriodNumberSelected and YearSelected, the possible values are not populated. Only the initial value is populated with a logical query filtered based on the value of MonthSelected. See the examples below.

Description of the illustration ceal_monthselected_parameter.jpg

Description of the illustration ceal_periodnumberselected_parameter.jpg