topic Re: Aggr & Fractile in QlikView

Aggr & Fractile

danielle_v — Fri, 27 Jan 2017 16:41:54 GMT

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!!

Re: Aggr & Fractile

sunny_talwar — Fri, 27 Jan 2017 17:23:57 GMT

May be try this:

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

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

Re: Aggr & Fractile

danielle_v — Mon, 30 Jan 2017 08:42:44 GMT

Hi Sunny,

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