The following scenario describes how to manipulate records in a dynamically computed range value.
GROUP
to calculate a range of interest.HAVING
to enable filtering by a dynamic value (HAVING
must be used because Diff is not in scope in a WHERE
clause on Result).DEFINE CustomerTotals AS SELECT SUM(SalesAmount) AS Total FROM SaleState GROUP BY CustomerKey ; DEFINE Range AS SELECT MAX(Total) AS MaxVal, MIN(Total) AS MinVal, ((MaxVal - MinVal)/10) AS Decile, MinVal + (Decile*9) AS Top10Pct FROM CustomerTotals GROUP ; RETURN Result AS SELECT SUM(SalesAmount) AS Total, Total - Range[].Top10Pct AS Diff FROM Range GROUP BY CustomerKey HAVING Diff > 0