Aggregate & Upper Quartile Help

[#1]

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!

[sunny_talwar]

For the first one you can try this (No Of Users over Median Call Length)

=Sum(Aggr(If(Median([Length Of Call]) >= Stdev(TOTAL<Unit> Aggr(Avg([Length Of Call]), Unit, User)), 1, 0), Unit, User))

I get 14 for Unit 3 because of these extra numbers which you might have missed

For the second expression (Total Number of extra Calls possible  if adhered to Median Call Length) how would we calculate this? I mean if you can share the logic, it might be easy to look at it.

[#3]

Hi,

I have worked out (in excel) what I need to have in Qlikview.  I have attached a sample XL file with the calculation.

So, as below, I need to calculate the total call time that could be saved where each call time is above the median call time...

Hope this helps... and many thanks again for your time.

TJM

[sunny_talwar]

May be this:

=Sum(Aggr(RangeMax([Length Of Call] - Median(TOTAL <Unit> [Length Of Call]), 0), Unit, User, caseno))

[#5]

Hi again,

Really very nearly almost there I think....  it seems to be calculating the total for the whole unit :

Thanks again! 

TJM

[sunny_talwar]

Here you are:

=Sum(Aggr(If(Median([Length Of Call]) >= Stdev(TOTAL<Unit> Aggr(Avg([Length Of Call]), Unit, User)), Sum(Aggr(RangeMax([Length Of Call] - Median(TOTAL <Unit> [Length Of Call]), 0), Unit, User, caseno))), Unit, User))

[#7]

a work of genius!!!!! Cant thank you enough!

[sunny_talwar]

I am glad I was able to help. One thing I would ask is to mark any helpful responses which you think might help future visitors to this thread. Don't overdo so that only helpful responses are marked as such

[#9]

Hi Again,

Hope you can help, I have had to add further data fields which may be skewing the calculations.  In the attached app I have had to include a higher level Site and also a Sub Spec field which is a 'child' of Spec.

Im having trouble getting my head around the 'Number of Users Over Median' calculation.. In the screen shot below should there only be 9 above the median for the Spec rather than 13?

whereas in the screenshot below it is correctly showing 12 as over the median for the Spec :

I'd appreciate any help you may have on this.

Many thanks again

[sunny_talwar]

I guess the calculation for Median is giving two outputs

1) Median(TOTAL<Spec> Aggr(Avg(Duration), Spec, User)) gives 8

2) Aggr(NODISTINCT Median(Duration), Spec) gives 18. Which one is right?

If the second one is right, try this

=Sum(Aggr(If(Median(Duration) >= Aggr(NODISTINCT Median(Duration), Spec), 1, 0), Spec, User))

But then the Spec4 becomes an issue. So may be this is what you need finally

=Sum(Aggr(If(Median(Duration) > Aggr(NODISTINCT Median(Duration), Spec), 1, 0), Spec, User))

Test it out and let us know if it works for you or not

[#11]

Hi That seemed to have worked - Thank you !  Could you just check the 'potential savings' calculation based on the new formula for those over the median?

Many many thanks again,