Insights recipe: Creating reports using date facts

by Bryan Castro, GoodData

When performing date arithmetic, it's necessary to use what we call Date Attributes . Date attributes are special because they can be used as a text field, as well as a numeric field where we can perform aggregations. Date attributes are integer representations of the dates, which enable us to do math operations on them.

For example, with the use of activity and registration date attributes, you can learn how many days have elapsed between a user's most recent activity and their registration date.

The metric for this example looks like this:

SELECT AVG(SELECT Activity Date - Registration Date BY Records of Registration )

You will notice that the AVG function is used here in conjunction with the connection point, also known as the primary key, of the Registration data set. The AVG function tells the application how to aggregate cases when a value needs to be computed for all users, or more than one user. The connection point is used to define at what level the date should be averaged. If the lowest granularity of data in this data set was individual users, then the metric would compute Activity Date-Registration Date per user. With the use of date attribute arithmetic, you can view the tickets that have not received updates in a certain period of time.

Creating your report

This recipe uses date facts to measure the amount of time since tickets were updated.

Note: In order to create this report, you will need to use the Advanced Metric Editor. For more information on how to create a custom metric, see the custom metric tutorial video on GoodData's support site.

Name your metric # Days No Contact
Type in SELECT AVG( THIS
From the pick-list Attributes folder select Date (Ticket Last Updated).
Click Add Selected to add to the metric.
Next, the connection point needs to be added into the aggregation. This will ensure that the metric pulls the Ticket Last Updated date from each Ticket Id.
Please confirm your screen matches the image below.

Making your report more meaningful

After you finish your metric, you can add attributes and filters to your report.

A helpful attribute for this report is Date (Ticket Last Updated) , which shows the last date a ticket received any update. Please note this attribute does not distinguish whether the update was made by the assignee or requester. In addition, you can add a numeric range filter to see the ticket IDs that have not been updated for over a week.

Here is the sample report created:

WHAT

# Days no Contact

HOW

Ticket ID
Ticket Assignee
Date (Last Updated)
Ticket Subject

FILTER

Ticket ID where # Day No Contact is greater than or equal to 7 (Numeric Range Filter)

Status IS New, Open, Pending (List of Values)

We hope you find this report useful!

Articles in this section

Creating your report

Making your report more meaningful

Comments