Zoomdata Version

Applying Filters to Custom Metrics

You can apply filters to your custom metrics. This means that attributes from the data can be applied as filter parameters to further refine and narrow your results. Specifying a filter lets you perform a calculation within a predefined range, for example, determining sales results within a certain period of time or within a certain demographic.

This topic covers the application of filters to custom metrics and provides instructions covering the operators and syntax for integrating a filter into your custom metrics.

Filtering Syntax

To create a custom metric that includes filters, operators or expressions are required. The operators supported in Zoomdata follow SQL logical expression syntax and grammar and include:

When integrating a filter within a custom metric, keep in mind that Zoomdata supports certain operators and follows a logical structure. In general, creating a filtered custom metric entails selecting the appropriate function variable, integrating the desired attribute or metric, using the appropriate filter operators and Date function, and entering a date in the correct format. Zoomdata calculations uses one of the following general structures:

<agg-function> WHERE <field> <operator> <values>
<agg-function> TRANSFORM <field> PreviousPeriod([offset,numPeriods])

where:

  • <agg-function> is any of the aggregate functions described in Supported Aggregation Functions.

  • WHERE or TRANSFORM is the appropriate SQL-like expression used for custom metric filtering. TRANSFORM is used specifically with the PreviousPeriod date and time filter aggregation function. WHERE and TRANSFORM filters are always applied to the broadest possible expression. For example, the following two expressions are both valid, but the first applies the filters to both SUMs, while the second one applies the filter only to SUM(Sales).

    SUM(Profit)/SUM(Sales) WHERE zipcode IN (90210,94107,92101)
    SUM(Profit)/(SUM(Sales) WHERE zipcode IN (90210,94107,92101))
  • <field> is a field (metric or attribute) in the data.

  • <operator> is one of the operators shown in the following table:

    Filters
    Capability Operators Notes
    Filtering on attribute options

    Single value: =, !=

    Multiple values: IN(), NOT IN()

    • Non-numeric fields such as Name, Address and State can be used as a filter.

    • References to an attribute in a field must be enclosed in single quotation marks. For example:

      SUM(Sales) WHERE State = ‘Florida’
    Filtering on date field options

    Date range operator: BETWEEN

    • Standard date formats (see Supported Date and Time Formats) are supported (such as 'yyyy-mm-dd', 'dd/mm/yyyy' including 'hh-mm-ss') .

    • Date (‘01-01-2015’) must be enclosed in single quotation marks.

    • Supported time periods are YEARS, MONTHS, WEEKS, or DAYS
    Filtering on numeric fields and calculation options >, <, =, !=
    • References to a numeric field must be enclosed in single quotation marks
    • References can be made to other calculations (using the calculation name)
  • <value> is an appropriate value, based on the requirements of the SQL-like expression. Values can be specific numbers or dates, or one of the date and time filter aggregation functions.

Examples

The following table provides examples of filtered custom metrics.

Filtered Custom Metric Examples
Capability Example
Filtering on Attribute options
SUM(Sales) WHERE State = ‘California’
SUM(Profit)/SUM(Sales) WHERE zipcode IN (90210,94107,92101)
Filtering on Date field options
MIN(Margin) WHERE Sale_Date BETWEEN ‘2013-01-01’ AND DateADD(MONTHS, 6, ‘01-01-2014’))
Filtering on numeric fields and calculation options
SUM(Sales)/(SUM(Sales) where User_Income = ‘0 to $25000’)

To calculate year over year growth:

((SUM(price) WHERE sale_date BETWEEN '2014-01-01' AND
 '2015-01-01') - (SUM(price) WHERE sale_date BETWEEN '2013-01-01' 
 AND '2014-01-01')) / (SUM(price) WHERE sale_date BETWEEN 
'2012-01-01' AND '2013-01-01') * 100
Comparing the difference between the current period to last period

Compare this period's deliveries to last period's deliveries:

SUM(deliveries) TRANSFORM delivery_date = PreviousPeriod()

Was this topic helpful?