Calculating fractile() across a set dimension regardless of selections

Tue, 14 Oct 2014 19:36:06 GMT

Hi,

Firstly thanks to anyone who takes the time to read and respond to this post. I've been going round in circles for a couple of days now and although I've learnt a lot about set analysis and used aggr() for the first time I'm now not making any forward progress so any help would be greatly received.

I have the following fields: Date, ClientName, TeamName, PersonName, TotalDuration, CallVolume

From these I need to calculate the Average Handle Time (AHT), the calculation for AHT is sum(TotalDuration) / sum(CallVolume), and then calculate fractile() thresholds.

Using the AHT figures for everyone working for a ClientName I need to generate quintile thresholds using fractile(), which will be stored as a variable, for each ClientName.

The fractile() thresholds need to be at a ClientName level and need to remain static when selections are made in either TeamName or PersonName fields, so if I select a PersonName the variable will still show the quintile threshold for the entire ClientName and not just that PersonName. The same is true for TeamName.

Using the below set analysis I can calculate the ClientName AHT and this doesn't change when I make selections using the PersonName/TeamName.

=sum({$<ClientName=p(ClientName), PersonName=, TeamName=>}TotalDuration)

sum({$<ClientName=p(ClientName), PersonName=, TeamName=>}CallVolume)

I know I can't calculate the fractile() with a single figure so I have to use the aggr() function. But I think the field used in aggr() to group by is taking precedence over the set analysis within the aggr() expression.

The below works perfectly if I only select the ClientName but as soon as I select the TeamName/PersonName the figure returned relates to the team/person selected.

=fractile

(

aggr

(

sum

(

{<ClientName =p(ClientName), PersonName=, TeamName=>} TotalDuration

)

sum

(

{<ClientName =p(ClientName), PersonName=, TeamName=>} CallVolume

)

,PersonName

)

, .2

)

I've tried to be as clear as possible but I'm sure I'll have missed something vital out, however I've also attached a sample qvw which has some example selections and my hoped for outcomes.

Many thanks in advance.

Re: Calculating fractile() across a set dimension regardless of selections

Wed, 15 Oct 2014 09:53:04 GMT

Hi All,

I've found a partial answer in: Using Fractile with a set modifier and combined this with setting a trigger under document properties for the TeamName and PersonName fields which forces a selection in ClientName.

So my final expression is:

=Fractile({<PersonName=, TeamName= >} TOTAL Aggr(Sum({<PersonName=, TeamName= >} TotalDuration) / Sum({<PersonName=, TeamName= >} CallVolume),PersonName),0.2)

topic Calculating fractile() across a set dimension regardless of selections in QlikView

Calculating fractile() across a set dimension regardless of selections

Re: Calculating fractile() across a set dimension regardless of selections