topic Re: Aggregate & Upper Quartile Help in QlikView

Aggregate & Upper Quartile Help

Mon, 30 Jan 2017 16:39:22 GMT

Hi,

In the attached sample app I have the following straight table :

I 'think' I have calculated the 'AVG Calls per Unit' correctly by using an aggregate function. Is there any reason why it is only showing in one cell?

Also, I need to create another expression where I need to calculate if each User carried out at least the average number of Calls for the unit - how many more calls could be made for this particular Unit.

Following on from this. If each user carried out the total calls per session at least equal to the upper quartile (top 5) of users how many more calls could be made ?

Any help or advice on this is greatly appreciated.

Many thanks!

Re: Aggregate & Upper Quartile Help

sunny_talwar — Mon, 30 Jan 2017 16:42:44 GMT

Try this instead of Aggr()

=Sum(TOTAL <Unit> total_calls)/Sum(TOTAL <Unit> total_sessions)

Re: Aggregate & Upper Quartile Help

Mon, 30 Jan 2017 16:49:22 GMT

Thanks for your quick reply! Any ideas on the upper quartile part of my query?

Re: Aggregate & Upper Quartile Help

sunny_talwar — Mon, 30 Jan 2017 16:52:26 GMT

What is the expected output you are hoping to see and where?

Re: Aggregate & Upper Quartile Help

Mon, 30 Jan 2017 17:08:20 GMT

Hi,

In the example below, I have taken the average of the Units's top 5 user's 'calls per session' which equals '2.94', then I need to create a new expression 'Calls to Upper Quartile' whereby for every user below 2.94 it calculates how many more calls could be made if they matched the rate of the top 5 :

if(user rate < average of top 5 , ([top 5 average] - [Calls/Session]) * Total Sessions)

This will be need to be calculated per unit...

many thanks for any help again - its really appreciated.

Re: Aggregate & Upper Quartile Help

sunny_talwar — Mon, 30 Jan 2017 17:25:40 GMT

Try this:

RangeMax((Avg(TOTAL <Unit> Aggr(If(Rank(total_calls/total_sessions) < 6, total_calls/total_sessions), Unit, user)) - total_calls/total_sessions) * total_sessions, 0)

Re: Aggregate & Upper Quartile Help

Tue, 31 Jan 2017 09:33:25 GMT

Perfect ! that's brilliant!!

One last thing - could a final expression be created to find the maximum calls/per session for that unit and create another column similar to the top 5?

Thanks again - your help has been appreciated.!

Re: Aggregate & Upper Quartile Help

sunny_talwar — Tue, 31 Jan 2017 10:16:52 GMT

May be this?

RangeMax((Avg(TOTAL <Unit> Aggr(If(Rank(total_calls/total_sessions) < 2, total_calls/total_sessions), Unit, user)) - total_calls/total_sessions) * total_sessions, 0)

Re: Aggregate & Upper Quartile Help

Tue, 31 Jan 2017 10:51:53 GMT

thanks again.. most helpful!

Will this solution also work in a pivot table with set analaysis within the expressions? I have tried to apply your solutions to my actual document and not getting the correct results....

Dimensions : Unit

User

My actual current expressions :

Total Sessions : =num(sum({< [DateValue]={'>=$(=date(vStart))<=$(=date(vEnd))'} >}benchmark_total_sessions),'0')

Total Calls : =sum({< [DateValue]={'>=$(=date(vStart))<=$(=date(vEnd))'} >}benchmark_calls_undertaken)

Calls/Session : =[Total Calls]/[Total Sessions] (which uses the two expressions)

Any ideas?

Many thanks again for your time

Re: Aggregate & Upper Quartile Help

sunny_talwar — Tue, 31 Jan 2017 12:02:31 GMT

I believe this should work...

RangeMax((Avg(TOTAL <Unit> Aggr(If(Rank(sum({< [DateValue]={'>=$(=date(vStart))<=$(=date(vEnd))'} >}benchmark_calls_undertaken)/sum({< [DateValue]={'>=$(=date(vStart))<=$(=date(vEnd))'} >}benchmark_total_sessions)) < 2, sum({< [DateValue]={'>=$(=date(vStart))<=$(=date(vEnd))'} >}benchmark_calls_undertaken)/sum({< [DateValue]={'>=$(=date(vStart))<=$(=date(vEnd))'} >}benchmark_total_sessions)), Unit, user)) - sum({< [DateValue]={'>=$(=date(vStart))<=$(=date(vEnd))'} >}benchmark_calls_undertaken)/sum({< [DateValue]={'>=$(=date(vStart))<=$(=date(vEnd))'} >}benchmark_total_sessions)) * sum({< [DateValue]={'>=$(=date(vStart))<=$(=date(vEnd))'} >}benchmark_total_sessions), 0)

But I would let you do the testing. In case it doesn't work, please provide an update sample for us to look at.

Best,

Sunny

Re: Aggregate & Upper Quartile Help

Tue, 31 Jan 2017 16:14:18 GMT

Once again this has worked like a dream!! many thanks.

One very utmost final request... ...

in the example below

is it possible to add a column similar to the .Opps to Top 5' where if the call/session is below the unit average it shows what could be achieved if every user aimed for the average (Col g)?

Also, can a column (J) be added to show the average of the TOP 5 for the unit for comparison values.?

Once again, I cant thank you enough for your help!

Re: Aggregate & Upper Quartile Help

sunny_talwar — Tue, 31 Jan 2017 16:40:39 GMT

Here you are

Re: Aggregate & Upper Quartile Help

Wed, 01 Feb 2017 09:23:38 GMT

excellent! Many thanks again for your expertise.!

Re: Aggregate & Upper Quartile Help

sunny_talwar — Wed, 01 Feb 2017 10:09:46 GMT

Not a problem

Best,

Sunny

Re: Aggregate & Upper Quartile Help

Mon, 13 Feb 2017 15:52:58 GMT

Hi Sunny,

Sorry to bother you again on this ... is there a way to create a 'median' calls per unit ?

Many thanks again,

TJM

Re: Aggregate & Upper Quartile Help

sunny_talwar — Mon, 13 Feb 2017 16:07:50 GMT

May be this?

=Median(TOTAL <Unit> total_calls/total_sessions)

Re: Aggregate & Upper Quartile Help

Mon, 13 Feb 2017 16:12:07 GMT

Outstanding - perfect!

TJM

Re: Aggregate & Upper Quartile Help

Wed, 15 Feb 2017 14:18:51 GMT

Hi again,

Hope you can advise on this? Just wondering if it is possible to show the following?

Basically, I need to have an expression that counts the number of users whose 'Median Number of Calls' are above the Standard Deviation for the Unit (Column H in the above mock up and column I in the 'expanded' view)

Once again, many thanks for your time.!

TJM

Re: Aggregate & Upper Quartile Help

sunny_talwar — Wed, 15 Feb 2017 16:38:12 GMT

Can you share an update app where you have calculated the standard deviation? I am not sure how you got 1.96

Re: Aggregate & Upper Quartile Help

Thu, 16 Feb 2017 10:03:15 GMT

Hi Sunny,

I attached a new sample QV document and also an excel sheet of what I'm trying to achieve.

Basically, I need to show how many users per unit are are over the median call time (column F in the image below) for each unit (we have decided to use median rather than mean). I then need to show what savings (in the form of extra calls handled) could be made per unit, if each user aimed for the median call time for their unit (column G in the image below):

I have tried to include a Standard Deviation per Unit - although as we are trying to work to Median LOS I'm not sure we can have a StDev for a Median?? Any thoughts on this.

Although this example is in Qlikview and the moment - we hope to implement it in QlikSense too.

Once again your time and expertise is highly appreciated!

TJM