Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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)