Aggr & Fractile

[danielle_v]

Hi All,

I'm currently trying to write an expression to show me what the on-time status for the client on the 50th percentile is - so based on the below table, the result I want to show is 40%

Client	On-Time %
A	5%
B	30%
C	40%
D	90%
E	95%

I have written the below expression, which gives me the result I'm after as long as no selections are made in the data. I need to be able to select a client though (in order to benchmark individual clients against various percentiles), and can't get the results to show correctly. I've tried various things to get the calculation to ignore selections in the Client field, including adding 'Client = ' into the set analysis, but no matter what I do, the result isn't correct when I make a selection on client

=fractile( 

aggr(
(sum( {$<OnTimeStatus={"On-Time"} LineDataCounter) / sum({$<OnTimeStatus={"On-Time", "Late"} LineDataCounter) )
, Client)
, 0.5)

Any help greatly appreciated!!

[sunny_talwar · Accepted solution]

May be try this:

=Fractile({<Client>}Aggr((Sum({$<OnTimeStatus={"On-Time"}, Client>} LineDataCounter)/Sum({$<OnTimeStatus={"On-Time", "Late"}, Client>} LineDataCounter)), Client), 0.5)

or

=Fractile(Aggr((Sum({$<OnTimeStatus={"On-Time"}, Client>} LineDataCounter)/Sum({$<OnTimeStatus={"On-Time", "Late"}, Client>} LineDataCounter)), Client), 0.5)

[danielle_v]

Hi Sunny,

Thanks for your help - the first solution seems to work perfectly!