Reporting M-N Metrics by Not Included Tags

This article expands upon Reporting M-N Metrics by Tag that describes how to create reports filtered by a single, specific tag in a M:N data model.

In that M:N article, you created a report whose metrics were filtered by the presence of an attribute value (Ticket Tag=customer). The # Tickets w/Customer metric looked like the following: 

Select # Tickets WHERE Ticket Tag = customer

This metric is not used in the report directly but is used to specify the filter of the report.

In this article, you create a similar metric to detect tickets that do not contain the Ticket Tag=customer attribute value. This metric is again used to filter the report.

Steps:

1. Create a new empty report.

2. Create the Tickets w/o Customer metric In the Custom Metric Editor which should look like the following:

   SELECT CASE WHEN (SELECT# TicketsWHERE Ticket Tag=customer)=1 THEN 0 ELSE # Tickets END
   

   This type of metric is structured to return a value of 0 if the condition of the WHERE clause is met. Otherwise, the value of the # Tickets metric is returned. In this instance, the WHERE test is Ticket Tag=customer.

3. Create a numeric range filter in your report. This filter should test for values of your Tickets w/o Customer metric is greater than 0:    

   
   

4. In your report, verify that the Tickets w/o Customer metric is not included in your report.

5. Create your report including the metrics to measure the non-customer tickets. Add attributes to the How pane to slice as needed.

   For reporting on metrics that have non-specific attribute values associated, you must create a metric to be used in the numeric range filter, similar to other M:N reporting. However, in the case of detecting absence of the attribute values, you must apply CASE/ELSE logic to the metric definition, looking only for values where the tag is not present.