PartitionBy function

The PartitionBy function divides query results into partitions. It supports multiple parameters, so you can create multiple partitions with one instance of PartitionBy. The calling function is applied to each partition separately and computation restarts for each partition. This function is available wherever the Statement Editor is used, including in app boards and dynamic views.

The syntax of the function is:

<table>.<field>.<function>.PartitionBy(<partition1>, <partition2>, ...)

where <partition> could be:

• <table>.<field>
• the result of another function such as If. For example, you could partition by the Pareto category provided by an If function.

PartitionBy can be used with OrderBy to order the data of each partition. If PartitionBy is used with OrderBy, OrderBy must appear after PartitionBy.

<table>.<field>.<function>.PartitionBy(<table>.<field>).OrderBy(<table>.<field>)

Use cases

The use cases for this function include:

• Calculating and ranking top production locations by volume or top sources for each product
• Intermediary calculations in model building

The PartitionBy function can be used to help create a running cumulative percent of an entire table, a running percent of total (Pareto analysis) or creating model sourcing splits.

Partitioning by location

The following example partitions orders by location and sums them by location:

Orders.quantity.Sum().PartitionBy(Orders.location)

This would give the following result from a sample Orders table:

Partitioning by two fields

In this example, orders are being partitioned by location ID and product ID:

Using OrderBy with PartitionBy

The OrderBy function can be used with the PartitionBy function to partition, accumulate, and order results by a field. If these functions are used together, Partition By must appear before Order By in the statement.

The following example partitions and accumulates orders by location, and orders them by quantity:

Orders.quantity.Sum().PartitionBy(Orders.location).PartitionBy(Orders.quantity)

This would give the following result from a sample Orders table: