topic Re: Calculating Percential/Fractile with Expression or Variable in App Development

Calculating Percential/Fractile with Expression or Variable

mp802377 — Thu, 22 Feb 2024 00:20:24 GMT

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

Re: Calculating Percential/Fractile with Expression or Variable

Lisa_P — Thu, 22 Feb 2024 04:21:05 GMT

Try this:

Fractile(Mean/Volume, 0.95)

You can't used a nested aggregation.

Re: Calculating Percential/Fractile with Expression or Variable

marcus_sommer — Thu, 22 Feb 2024 12:21:30 GMT

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.

Re: Calculating Percential/Fractile with Expression or Variable

mp802377 — Thu, 22 Feb 2024 13:56:54 GMT

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.

Re: Calculating Percential/Fractile with Expression or Variable

marcus_sommer — Thu, 22 Feb 2024 14:51:34 GMT

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.