Page tree
Skip to end of metadata
Go to start of metadata

You can use data arithmetic when you are computing sales velocity, support ticket response times, or any other duration metric.

GoodData now supports arithmetic applied directly to date attributes. This reduces LDM complexity, improves reporting performance, and streamlines metric definitions.

To create a duration metric with date attributes, subtract the start date from the end date at the desired level of granularity (date, week, month, quarter, and so on).

SELECT Date (End Date) -  Date (Start Date) BY [connection point]

The [connection point] represents the primary key of the data set to which the date dimensions belong. The BY statement specifies the granularity at which the date arithmetic is being carried out.

Examples

Event duration

You want to measure the duration of events. The events have a start date and an end date.

To specify that the metric should be aggregated at the event level, add the event attribute to the report from the How pane.

The resulting report displays the event duration metric broken down by event.

Average event duration

To compute the average event duration, the metric must:

  • Compute the duration for each event.
  • Return the average of these duration values.

To compute the duration of each event , subtract the start date from the end date and add the statement that specifies that the duration being computed is for each individual event:

SELECT Date (End Date) - Date (Start Date) BY Event

To return the average, wrap the metrec within an average function:

SELECT AVG((SELECT Date (End Date) - Date (Start Date) BY Event))


The inner metric in the syntax above is known as a sub-metric, which in MAQL is always surrounded by double parentheses. If you define the sub-metric separately and reference it within another metric, use single parentheses:

SELECT AVG(Event Duration)

In this case, the Event Duration metric is pre-defined, and has the following syntax:

SELECT Date (End Date) - Date (Start Date) BY Event

To keep the MAQL syntax simple and error-free, define sub-metrics externally and then reference them, as demonstrated above. 

The result of the new metric is a single metric value that represents the average event duration.

Remove the event attribute from the report and change the visualization mode to Headline to display a single number:

To see the Average Event Duration for each Type of event, add the Type attribute to the report:

Complex examples

Attribute arithmetic enables advanced duration computations. For a more complex example, see article on explicit lifting. This article helps you to compute the average amount of time that elapses between returning customers’ first two transactions.

You can also see our article on finding mix and max dates using data arithmetic.


Handling Empty Values

Empty date values are converted to zeroes (0), which may deliver unexpected arithmetic results.

If your source data may contain empty values, create a flexible metric to remove the empty values from the computation. For example, you have the following metric:

SELECT AVG(Merchant Live Date-Merchant Create Date)

Empty values for either of the date values generate zero values, which would create disrupting outliers to the overall average computation.

You can modify this metric calculation to remove empty date values using the following custom metric:

SELECT AVG((select Date (Merchant Live Date) - Date (Merchant Create Date) BY Estore ID
  WHERE Date (Merchant Live Date)   <> (empty value)
    AND Date (Merchant Create Date) <> (empty value)))