Zoomdata Version

Applying Filtered Metrics (in Calculations)

Overview

With Zoomdata Calculations functionality, you can apply filters to your formulas. This means that attributes from a dataset can be applied as filter parameters to further refine and narrow your results. Specifying a filter lets you perform a calculation within a predefined dataset range, for example, determining sales results within a certain period of time or within a certain demographic (see Figure 1 for an example).


Figure 1

This article covers the application of filters in calculations and provides instructions covering the operators and syntax for integrating a filter into your formulas. For an overview of the Calculations functionality and how to build formulas using the Editor, refer to the article Creating Calculations in Zoomdata .

Filter Parameters

In order to create a calculation to include filters, the use of operators (or expressions) is required. The operators supported in Zoomdata follow the SQL syntax/grammar in the creation of logical expressions, which include:

  • WHERE

  • AND, OR

  • BETWEEN

  • IN, NOT IN

  • TRANSFORM

Zoomdata offers different types of filters for calculations. Table 1 shows the different filter types that are supported:

Table 1: Filter Capabilities
Filters
Capability Operators Description
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

  • Example: SUM(Sales) WHERE State = ‘Florida’
Filtering on Date field options
  • Date range operator:
    BETWEEN

  • Current date operator:
    DATE()

  • Time periods:

    • YEARS

    • MONTHS

    • WEEKS

    • DAYS
  • Standard date 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

Filtering on Date Operator: TRANSFORM
  • This operator is used specifically with the 'PreviousPeriod()' function to calculate a selected time range to an earlier time period.

  • PREREQUISITE: Transformation to PreviousPeriod() works only if date field used in the formula is selected either on the time bar or in the time filter.

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)
*Refer to the article Date and Time Formats Supported By Zoomdata for the comprehensive list.

Filter Parameters for the Date Function

The Date function is primarily used to filter a calculation by a specific time range or time interval. You can filter a calculation by the following time parameters:

  • By a specific date (such as ‘2015-01-01’) enclosed in single quotes

  • By a time range (such as  BETWEEN ‘2015-01-01’ AND date())

  • By a length of time (such as 1 year or 2 months)

  • By using TRANSFORM to identify an earlier time period so that data values between different time periods can be compared (such as SUM(Sales) TRANSFORM 'Current Year' = PreviousPeriod())

As detailed in the article Creating Calculations in Zoomdata , the following Date functions are currently supported:

  • DateADD(time_period, interval, date)

  • DateSUB(time_period, interval, date)

  • PreviousPeriod(offset, numPeriods)

Specific parameters are needed for a valid Date function. See Table 2 for a breakdown of each parameter and the value that is accepted for each.

Table 2: Date Function Parameters
Parameter Value
time period

Supported time periods (with corresponding interval range):

  • YEARS

  • MONTHS

  • WEEKS

  • DAYS
interval Whole number integer
date
  • Current day operator: date()

  • Standard date and time formats supported by Zoomdata, including:

    • yyyy-MM-dd HH:mm:ss

    • MM/dd/yy hh:mm aa

    • yyyy

For all supported formats, view the article Date and Time Formats Supported By Zoomdata .
offset Optional time granularity for the previous period (includes day, week, month and year).
numPeriods Optional argument specifying the number of periods to go back in time. Default should be 1.

PreviousPeriod()

The PreviousPeriod() function is used for comparing data values between different time periods. This function can be used when you need to compare one time period to another of equivalent size for variance calculations. For example, comparing results from the current month to the previous month or the current week to the same week one year ago.

However, note that this function will only work when the date field used in the formula is selected on the time bar.

In order to use this function, the operator ​​ 'Transform' must be used in the calculation to convert the date range for a specified time attribute. For example:

SUM(Sales) TRANSFORM saledate = PreviousPeriod()

If the 'saledate' time period was set from Jan 1, 2015 through Dec 31, 2015, then the calculation would return SUM(Sales) where 'saledate' is from Jan 1, 2014 through Dec 31, 2014.​

Filtered Calculation Syntax

When integrating a filter within a calculation, keep in mind that Zoomdata supports certain operators and follows a logical structure. In general, creating a filtered calculation 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 the following logical structure (as shown in Figure 2):

Figure 2

Table 3 provides examples of filtered calculations.

Table 3: Examples of Calculations Containing Filters

Filtered Calculation 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
  • (AVG(Profit) WHERE Sale_Date BETWEEN ‘2015-01-01’ AND date())

  • 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
  • SUM(deliveries) TRANSFORM delivery_date = PreviousPeriod()
    Compare this period's deliveries to last period's deliveries