|Related Tags: maql metrics dates reporting|
A key feature in reporting is the ability to pull data based on the first or last dates when specific events occurred. This comes in handy when, for example, support agents want to uncover the oldest unsolved tickets in their help desk queue.
In the past, these types of metrics were created using date facts, numerical data modeled in the logical data model (LDM), and loaded into the project. Now that GoodData supports date arithmetic with date attributes, the same logic can be applied in metrics, using less complex metrics and a simpler LDM.
NOTE: The one difference between the old way of aggregating date facts and the new process of aggregating date attributes is in the metric syntax, or MAQL. For those familiar with working with date facts, this new syntax is a slight departure from the logic used previously.
We’ll discuss a use case specific to the LDM below, which shows the relationships between events, including their start/end dates, and the towns where they occurred.
In this example, we want to understand which type of event occurred first in each town. To find this value, we’ll take a few simple steps using date arithmetic to find the first, or MIN, Date (Start Date) that occurred in each Town.
Connecting dates to events
Before we can find this value, we need to create a submetric to allow our Start Date values to connect back to our connection point (Event).
NOTE: This new syntax wasn’t previously necessary when aggregating date facts. It’s required to join the date back to the rest of the dataset. When reading the LDM diagram, it’s important to understand that facts and attributes can only be sliced and diced by objects directly connected to the left of them.
In this case, Date (Start Date) has no objects connected to its left, so we must rejoin it to its connection point (Event) with MAQL:
SELECT Date (Start Date) BY Event
The BY clause is used to allow the metric to interact easily with the rest of the Event and Town datasets.
Identifying the first (earliest) date
Next, additional syntax is added to find the first Start Date that occurred in each Town. This metric will be called “First Start Date Per Town.”
SELECT MIN ((SELECT Date (Start Date) BY Event)) BY Town, ALL OTHER
Here, the BY Town, ALL OTHER clause is added so that each Town returns one date – the date of the first event that occurred there – even if multiple events have occurred in each Town. This date is represented by a numeric value rather than an actual date:
Inserting the metric into your report
The final step is to plug the previous metric into one final metric, which will find the number of events that occurred on that specific date:
SELECT COUNT (Event) WHERE Date (Start Date) = First Start Date Per Town
The “ First Start Date Per Town” metric above is used to filter the metric to only count the first event that occurred in each town.
With the final metric, we can now see which Event and Type occurred first in each town. All subsequent Events have been filtered from the report based on our metric definitions:
Hope that helps to deliver better insight into major events in your data stream. Please let us know how it goes. You can also check out our article on date attribute arithmetic for more information on working with dates.