Date Attribute Arithmetic

Related Tags: reporting explicit lifting metrics maql

Whether you’re computing sales velocity, support ticket response times, or any other duration metric, date arithmetic will come in handy. Previously this could be done in GoodData by creating date facts out of date attributes and then operating on those facts. But now GoodData 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, you can now simply subtract the start date from the end date at the desired level of granularity (date, week, month, quarter, etc.).

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

In this case, [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. We’ll explore this further in the following example, which uses an Event connection point to carry out date attribute arithmetic.

Consider the case where we’re measuring the duration of events – each of which having a start date and end date.

Sample logical data model (LDM)

We’ve already set up our duration metric so all that remains is to specify that the metric should be aggregated at the event level. We can do this at the report level by simply adding the event attribute to the report from the How pane.

The result is a report displaying our event duration metric broken down by event.

event duration
Event duration by event

If we instead wished to compute the average event duration, we’d have to redefine our metric accordingly. Our new metric would need to do the following:

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

To meet the first objective we’ll need syntax similar to that of our initial metric, coupled with a statement that specifies that the duration being computed is for each individual event.

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

We can then return the average of all the values returned by this metric by wrapping it within an average function. Our new metric will take the following form:

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

NOTE: The inner metric in the syntax above is known as a sub-metric, which in MAQL is always surrounded by double parentheses. The exception to this rule is when the sub-metric is defined separately and is merely being referenced within another metric. In that case, single parentheses are used:

SELECT AVG(Event Duration)

In this case, the Event Duration metric would be pre-defined, and would have the following syntax:

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

Rather than define sub-metrics within parent metrics, it is considered best practice to define sub-metrics externally and then reference them, as demonstrated immediately above. This helps keep MAQL syntax simple and error-free.

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

When we remove the event attribute from our report and change the visualization mode to Headline, our output will appear as follows:

average duration headline
Average duration headline report

And to see the Average Event Duration for each Type of event we could add the Type attribute to our report while leaving the metric’s definition unchanged.

average duration by type
Average duration by event type

With date attribute arithmetic, there are many new possibilities for carrying out advanced duration computations. For a more complex example, see our article on explicit lifting, where we compute the average amount of time that elapses between returning customers’ first two transactions. You can also check out our article on finding mix and max dates using data arithmetic.

explicit lifting for time duration metrics
Explicit lifting example from another project

Are repeat customers likely to purchase more if less time elapses before their second purchase? Date attribute arithmetic allows us to zero in on answers to just such a question.

Handling Empty Values

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

If you suspect that your source data may contain empty values, you can create a more flexible metric to remove the empty values from the computation. For example, suppose your base metric is the following:

SELECT AVG(Merchant Live Date-Merchant Create Date)

In the above, empty values for either of the date values would generate either 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)))