Comparing n'th row with the next row & calculate percentile of the calculated field

Hi,

I have a scenario to calculate the time-difference between each snapshots received in a month for a Vendor & vendor Site.

I was able to calculate the time difference between the subsequent snapshots by creating 2 calculated fields.

LeadDate :

lead(
    {snapshot_time_utc},
    [{snapshot_time_utc} DESC],
    1,
    [vendorname]
)

&

DateDiffWithLead
dateDiff(LeadDate,max({snapshot_time_utc}),'MI')

The data comes up in this format in the visual as :

Now my requirement is to calculate the average of (Percentile 90) all the time duration for a month for a site
Required Output. But I am unable to add any more calculations on the field DateDiffWithLead

Vendor | Site Name | Avg (Percentile 90 Hours)

Please help me, if I am missing some thing.

Thanks

1 Like

Hello @indraban , welcome to the QuickSight community! I have an idea to resolve this, can you try adding this calculated field for me:

percentile({DateDiffWithLead}, 90, [vendorname])

Also, I’ll include this link for the documentation on the percentileDisc functionality. The LAC-A function I think would be best suited for your solution.

Hello @indraban , did my response resolve the issue you were facing in QuickSight? If so I can mark it as a solution!