How to Filter by Partition in a Workbook - Advanced Filtering

Goal

When an Import Job or Data Link is partitioned in Datameer, users may utilize the Filter by Partition feature of a workbook. One powerful type of filter for partitions in a Workbook is Advanced Filtering.

An Advanced Filter allows a user to create a Datameer function including a $partition variable that evaluates to a Boolean value. All partitions will be tested using the $partition variable and those that evaluate to True will be included in the Workbook's data set.

 

Learn

Understanding the $partition Variable for Different Partition Resolutions

When a partition is created in Datameer for an Import Job or Data Link, there are four different partition resolutions that may be configured: YEAR, MONTH, DAY or HOUR. The $partition variable is a DATE type object in Datameer. This variable is set to the start of the partition window. Depending on the resolution of the partitions, the $partition variable takes on different DATE values. 

For example, suppose that Apache log data is being imported from July 4, 2014 22:00:00 until July 4, 2014 22:59:59. The following table describes how an Import Job would partition data for each possible resolution. Additionally it describes what the corresponding $partition variable would be for this data set:

Partition ResolutionPartition Range (Inclusive)$partition Variable Value
YEAR January 1, 2014 00:00:00 - December 31, 2014 23:59:59 January 1, 2014 00:00:00
MONTH July 1, 2014 00:00:00 - July 31, 2014 23:59:59 July 1, 2014 00:00:00
DAY July 4, 2014 00:00:00 - July 4, 2014 23:59:59 July 4, 2014 00:00:00
HOUR July 4, 2014 22:00:00 - July 4, 2014 22:59:59 July 4, 2014 22:00:00

The $lastpartition Variable

In Datameer 2.1.7 and higher, another variable is available for use when defining an Advanced Filter for partitions in a Workbook. This variable, $latestpartition, represents the most recent partition that contains data. 

 

Additional Information

Advanced Filter Example - Selecting a Specific Partition

For example, let's suppose that a user would like to select a partition that contains the time from precisely 1 day ago. The following table describes how to configure an Advanced Filter to select the partition that includes precisely 1 day ago:

Partition ResolutionAdvanced Filter Formula to Select the Partition for 1 Day Ago
YEAR
YEAR($partition) == YEAR(NOW()-1d)
MONTH
YEAR($partition) == YEAR(NOW()-1d) && MONTH($partition) == MONTH(NOW()-1d)
DAY
YEAR($partition) == YEAR(NOW()-1d) && MONTH($partition) == MONTH(NOW()-1d) && DAY($partition) == DAY(NOW()-1d)
HOUR
YEAR($partition) == YEAR(NOW()-1d) && MONTH($partition) == MONTH(NOW()-1d) && DAY($partition) == DAY(NOW()-1d) && HOUR($partition) == HOUR(NOW()-1d)

Of note, the following function would not produce the expected result in all circumstances:

$partition == NOW()-1d

This is also thru for

$partition == TODAY()-1d