Calculating fractile() across a set dimension regardless of selections

[#1]

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.

[#2 · Accepted solution]

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)