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).
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:
Filters  

Capability  Operators  Description 
Filtering on Attribute options 


Filtering on Date field options 


Filtering on Date  Operator: TRANSFORM 

Filtering on numeric fields and calculation options  >, <, =, != 

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 ‘20150101’) enclosed in single quotes

By a time range (such as BETWEEN ‘20150101’ 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.
Parameter  Value 

time period 
Supported time periods (with corresponding interval range):

interval  Whole number integer 
date 
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.
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:
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):
Table 3 provides examples of filtered calculations.
Filtered Calculation Examples  

Capability  Example 
Filtering on Attribute options 

Filtering on Date field options 

Filtering on numeric fields and calculation options 
((SUM(price) WHERE sale_date BETWEEN '20140101' AND '20150101')  (SUM(price) WHERE sale_date BETWEEN '20130101' AND '20140101')) / (SUM(price) WHERE sale_date BETWEEN '20120101' AND '20130101') * 100

Comparing the difference between the current period to last period 
