Skip to main content

Combined Function Usage Cases

Case 1: Combine Aggregation Functions and Conditional Functions

Perform sum aggregation only when a certain condition is met.

The common approach requires pulling an additional branch with a filter and then stitching it back to the original logic. The combined formula below reduces steps and improves accuracy.

Example: sum + case when

sum(
case when [Route Level] = 'T1'
then [Returned Bottle Boxes]
else 0 end)
over(partition by [End-to-End Route], [Accounting Period], [Actual Route], [Whole Course Route])

image.png

Case 2: Cross-Row Assignment with first_value

  1. Create a field [Sort] = case when [Route Level] = 'T1' then 1 else 2 end.

image.png

  1. first_value([T1 Level Returned Bottle Boxes]) over(partition by [End-to-End Route], [Accounting Period], [Whole Course Route] order by [Sort])

Note: Row 5 in the screenshot is a WS segment. Its original returned bottle box count is 20, but it needs to take the volume from the T1 segment.

image.png

Extension: Combine Case 1 and Case 2

first_value(
sum(
case when [Route Level] = 'T1'
then [Returned Bottle Boxes]
else 0 end)
over(partition by [End-to-End Route], [Accounting Period], [Actual Route], [Whole Course Route])
)
over(partition by [End-to-End Route], [Accounting Period], [Whole Course Route] order by [Sort])

image.png