SQLServer, HIVE, and several other data sources support SQL Functions: LEAD & LAG; they are rather cryptic functions even for those familiar with SQL. It would be incredibly helpful and powerful if there was a way to expose these functions within the UI so they become readily available even to those who have no knowledge of SQL. I haven’t fully thought out the flow of how this would look from a UI perspective but on surface seems ultimately like a viable enhancement.
Not sure if this will help, but here is a query I ran against HIVE that only returns rows where the Plant’s average monthly target for the subsequent month is greater than the current monthly target:
Select PlantName
, Average_MonthlyTarget
, lead_AVG_Target
, lag_AVG_Target
from (SELECT PlantName
, Average_MonthlyTarget
, lead(Average_MonthlyTarget,1) over (partition by PlantName order by PlantName asc) as lead_AVG_Target
, lag(Average_MonthlyTarget,1) over (partition by PlantName order by PlantName asc) as lag_AVG_Target
FROM “GMM Scorecards”.“GCR Clean18”)
where lead_AVG_Target > Average_MonthlyTarget
So in essence would want to be able through UI to accomplish this query. Maybe on the column selection drop down, one would be presented with a “Compare to Next” and “Compare to Previous” options. Once one selects one of these choices they would be presented with another drop down list of columns to select the partition by column. This way the SQL could be generated automatically for the inner query. One could then just create another VDS for the outer query to get the appropriate Where clause.
A simpler option might be to just include LEAD & LAG as available function in the Calculated Field available functions. This would be a good first step, but would not provide much help for those who aren’t that skilled with SQL.