|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.
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.
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))
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 EventRather 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:
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.
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.
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)))