Pega Reports with percentile function
Hello Support,
We have a requirement to generate a pega report that shows the 96 percentile of elapsed time for a service request type in 10 minute intervals.
Using below sql query we are able to extract the information but how to implement it on Pega reporting definition.
By default there are no function(fx) available for percentile calculation. Could you please guide us to implement this requirement.
SELECT date_trunc('hour', pxCreateDateTime) + date_part('minute', pxCreateDateTime)::int / 10 * interval '10 min' as Interval, percentile_disc(0.96) within group (order by TotalElapsedTime asc) FROM pegadata.WORK where pxCreateDateTime>=date_trunc('day',Current_date-10) and ServiceRequestType in ('TTcreate') group by date_trunc('hour', pxCreateDateTime) + date_part('minute', pxCreateDateTime)::int / 10 * interval '10 min'