User Prompt Filter at View Level
I have a Yellowfin View which has just the one virtual table VT The code for VT is:
SELECT ID1 ,ID2 ,Name1 ,Date1 ,DATEDIFF(day, LAG(Date1,1) OVER (PARTITION BY ID1,ID2), Date1)) AS DayDiff FROM T1This basically finds the number of days between subsequent Date1 values for ID1, ID2 combinations. My report based off this view would be to find the Average of this DayDifference. Date1 needs to be a user prompt filter so that averages are calculated based on the date range entered. The auto-generated report code is:
SELECT ,VT.ID1 ,VT.ID2 ,AVG(VT.DayDifference) As AvgDayDifference FROM ( SELECT ID1 ,ID2 ,Name1 ,DATEDIFF(day, LAG(Date1,1) OVER (PARTITION BY ID1,ID2), Date1)) AS DayDiff FROM T1 ) VT WHERE VT.Date1 BETWEEN ' ' AND ' ' GROUP BY VT.ID1 ,VT.ID2Since the Date1 Filter is not inside the subquery but outside it, the Average is calculated for all Date1's and not for the Date's entered in the UPF
How can I do this using the Drag&Drop Builder? I don't believe there's a UPF for Views which can be linked to Report UPF.