Skip to main content

Combined Function Usage Cases

Case One: Combination of Aggregate Functions and Conditional Functions

When xx conditions are met, then perform sum aggregation;

The general approach requires using a filter to pull an additional branch, then splice it back to the original logic. Using the following combination formula reduces steps and is more accurate.

【Example】sum+case when

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

image.png

Case Two: Cross-row Assignment first_value

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

image.png

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

Note: Row 5 in the screenshot is the WS segment, originally its own return bottle box count was 20, but needs to take the T1 segment amount

image.png

Extension: Combined Use of Cases One and Two

first_value(
sum(
case when [Route Level] = 'T1'
then [Return Bottle Box Count]
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