Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table similar to below. I am looking to find the .95 percentile for Average Response. Average response is calculated as Mean/Volume. If I put in Fractile(Volume, .95), that works. But if I try Fractile((sum(Mean)/sum(Volume)), 0.95), the expression is marked as okay, but I get - . I thought maybe it needed to be a variable. I created a variable using the expression (sum(Mean)/sum(Volume) and tried Fractile ($(vResponseTimeDay), 0.95) and still get an -
How would I get the Percentile or Fractile in this case?
Date | Customer Name | Volume | Mean | Average Response (Mean/Volume) | Fractile 95 on Volume |
02-28-2023 | Customer A | 3,339,262 | 4387751.459 | 1.3140 | 2,837,607 |
02-28-2023 | Customer B | 409,173 | 719415.472 | 1.7582 | 388,633 |
02-28-2023 | Customer C | 1,515,048 | 3252159.315 | 2.1466 | 1,438,936 |
02-28-2023 | Customer D | 10,086,979 | 13309029.579 | 1.3194 | 8,571,953 |
Try this:
Fractile(Mean/Volume, 0.95)
You can't used a nested aggregation.
Fractile() is an aggregation-function like sum() which couldn't be nested without using an aggr() to specify the dimensionally context of the outer-aggregation. This means your expression might be lloking like:
fractile(aggr(sum(X) / sum(Y), Dim1, Dim2))
whereby DIm1 and Dim2 are just placeholder for the needed dimensionality - usually the dimensions from the chart.
If all your records look like the screenshot and should be displayed in such way you wouldn't need the inner-aggregations else you could apply the suggestion from @Lisa_P.
That doesn't work unfortunately. Using the calculation Fractile(Mean/Volume, 0.95) for Customer A gives a result of 12. But, if I do a Fractile (1.3140, 0.95) the result is 1.
Fractile() on a single value isn't sensible - it could just return the value itself. If you enables to show more digits you should see it. Fractile() on a field will consider all possible values in regard to the used selections as well as to the applied object-dimensions and will take all available values - not only those which are visible because all objects show only distinct values in regard to the object-dimensions - any duplicates of values aren't not displayed but used in the calculation.