Date Attribute Arithmetic

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 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:

Handling Empty Values

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)))