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.
- Modify the
Month
prompt to add the presentation variable. - Create a separate dashboard prompt for
Julian Period Number
.
Description of the illustration ceal_julian_period_number_prompt.jpg - Edit your dashboard properties and click Filters and Variables.
Description of the illustration ceal_dashboard_properties.jpg - Add the
Julian Period
prompt.
Description of the illustration ceal_dashboard_filters_variables.jpg - Next, in the analysis, delete the global filter on the
Month
column, and use the column formulas filter functions based onJulian 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