Zoomdata Version

Supported SQL-Like Expressions

Zoomdata's custom metrics support the following SQL-like expressions:

Expression Description
WHERE

Use WHERE to filter by a condition. Data will only be included in the custom metric if the condition that follows is true. For example:

COUNT(accidentnumber) WHERE airportcode IN ('LAX', 'ORD', 'IAD')
AND

Use AND to form a conjunctive condition. Data is only included in the custom metric if it meets both of the conditions connected by AND. The following example calculates the sum of totalfatalinjuries only if the broadphaseofflight includes LANDING and the airportcode is LAX.

SUM(totalfatalinjuries) WHERE broadphaseofflight IN 'LANDING' AND airportcode='LAX'
OR

Use OR to for a disjunctive condition. Data is included in the custom metric if it meets either of the conditions connected by OR.  The following example calculates the sum of totalfatalinjuries if the broadphaseofflight includes LANDING or the airportcode is LAX.

SUM(totalfatalinjuries) WHERE broadphaseofflight IN 'LANDING' OR airportcode='LAX'
BETWEEN...AND

Use BETWEEN to filter using a range of values. The following example counts the number of distinct records for accidentnumber that have totalminorinjury counts between 2 and 10.

COUNTD(accidentnumber) WHERE totalminorinjuries BETWEEN 2 AND 10
IN

Use IN to filter using a set of values. Data is included in the custom metric only if a data field matches one of the listed values. The following example calculates the sum of totalfatalinjuries only for records in which the airportcode field is LAX, ORD, or IAD.

SUM(totalfatalinjuries) WHERE airportcode IN ('LAX','ORD','IAD')
NOT IN

Use NOT IN to filter using a set of values. Data is included in the aggregation only if a data field does not match one of the listed values. The following example calculates the sum of totalfatalinjuries only for records in which the airportcode field is not LAX, ORD, or IAD.

SUM(totalfatalinjuries) WHERE airportcode NOT IN ('LAX','ORD','IAD')
TRANSFORM

Use TRANSFORM to filter based on a derived date. To derive a date with TRANSFORM, you must already have a time attribute configured in your data source. The following example calculates the sum of totalfatalinjuries only for records in which the eventdate is for the previous period. In other words, if the chart is examining two weeks of data for totalfatalinjuries, this calculation will provide data about the two weeks prior to that.

SUM(totalfatalinjuries) TRANSFORM eventdate=PreviousPeriod()

To work correctly, data must be available for the periods of time considered.


Was this topic helpful?