Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Average of part of the list of numbers Post Reply Post New Topic
Author Message
muncy
Newbie
Newbie


Joined: 12 Oct 2011
Online Status: Offline
Posts: 6
Quote muncy Replybullet Topic: Average of part of the list of numbers
    Posted: 05 Feb 2013 at 6:33am
I'm tying myself in knots trying to write a formula. I'll try to explain.
 
I have formula that counts the number of days between two events on tickets. This is used in the report so it looks like:
 
Ticket1 45
Tic ket2 07
Tic ket3 145
Ticket4 23
Ticket5 62
 
etc (There will normally be a minumum of about 30 tickets.
 
What I want to do is disregard the best 10% and the worst 10% then do an average on the rest. So, in the case of the above, 07 and 145 would be disregarded and the remaining values would be averaged (43.33). Of course, depending on the number of values returned it isn't always going to be 1 of the top and the bottom. It needs to be 1 or 10% whichever is higher.
 
Any help appreciated.
 
 
IP IP Logged
Sastry
Moderator
Moderator
Avatar

Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
Quote Sastry Replybullet Posted: 05 Feb 2013 at 10:50pm
HI

Step 1 :

Find out the Maximum & Minimum values and it's percentages

Maximum 10% could be : Maximum - (Maximum *.10)
Minimum 10% could be  : Minimum + (Minimum*.10)

Step 2 :

Find out the count of Min and Max values like

Numbervar min;
if {No.of.Days} < Minimum + (Minimum*.10) Then
   min:=min+1;

Numbervar max;
If if {No.of.Days} <  Maximum - (Maximum *.10) Then
max:=max+1;

Step 3 :

Find out whether your min & max count is more than 1 or not and suppress those records through selection expert :

If min > 1 Then
{No.of.Days} < Minimum + (Minimum*.10)
Else
Minimum({No.of.Days}

Follow the same logic for Maximum.

You need to write a running total to arrive average.





Thanks,
Sastry
IP IP Logged
muncy
Newbie
Newbie


Joined: 12 Oct 2011
Online Status: Offline
Posts: 6
Quote muncy Replybullet Posted: 06 Feb 2013 at 3:16am
This has given me what I want. Thank you so much
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.