?
Solved

Excel Trimean calculation in Crystal

Posted on 2006-03-28
10
Medium Priority
?
681 Views
Last Modified: 2007-12-19
I have a report that works out the average of a series of numbers using the build in summary fuction but I need to work out the trimean of the same sets of numbers. For those unaware of what trimean is (like I was) it is an average of the numbers after discarding the top and bottom 10 percent of the numbers.

The numbers are contained within a group and I will need to work out the total trimean figure for multiple groups at the end of the report, I imagine this will be fairly easy once I know how to do it for one group.

I will appreciate any help in doing this, it feels like it should be easy but my brain seems insufficient for the task this week (possibly less late nights would help :) )
0
Comment
Question by:Gryff
10 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 16309152
Hi Gryff,
Do you discard based on value or based on position in the list?

Pete
0
 

Author Comment

by:Gryff
ID: 16309264
The discard is literally the 10% highest value numbers and the 10% lowest value numbers.  I have included below the description I am going by. The example is a small set of numbers so it appears to be just removing the highest and lowest number but if the data set was larger there would be more exclusions.

"Trimean=is an Excel function where by Trimean calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set. This function can be used to to exclude outlying data from our analysis.  The example below excludes 20% which is 10% from the lower numbers and 10% from the upper numbers"
Example                  
      Trimean            Ave
      4            4
      5            5
      6            6
*excluded      20            20
      6            6
      3            3
      4            4
      5            5
*excluded      1            1
      4            4
      5            5
      4.666666667            5.727272727
0
 
LVL 42

Accepted Solution

by:
frodoman earned 1000 total points
ID: 16310782
Your best bet is to create a UFL (user function library) and use vb, etc. to write the code for this function.  There's a document with instructions here: http://support.businessobjects.com/communityCS/TechnicalPapers/scr_user_defined_functions.pdf.asp

It should be possible to do it within Crystal but it would be a major pain.  First you'll need to read through all of the records and put them into a dynamically sized array.  Then sort the array and count the elements and figure out which elements get dropped (don't forget special cases like what, if anything, gets dropped if there are only five items in the list, if the calculations call for dropping 1.5 elements on each end do you drop 1 or 2 on each end or drop 1 on the low end and 2 on the high end, etc.).  Once you know which items to ignore you can simply add the remaining elements and divide.

Unfortunately array management in Crystal can be frustrating so although implementation is possible in Crystal it'll be much easier outside.
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 

Author Comment

by:Gryff
ID: 16311472
Hmm, looking through the documentation you linked to I am not sure this will be possible. The report is being made for another companies intranet so I assume this DLL would need to be loaded onto their intranets webserver and incorporated into their pages in someway. Even assuming this is allowed and someone here knows how to do it (both unlikely :( ) I think the time it would take to write the .dll is not practical for us.

The problem I have is that the Trimean seems trivial to do in excel, but creating a dynamic report with varying number of groups is difficult (at least for me) if not impossible in excel but the reverse is true for crystal. I think projects like this are created to punish me for a mispent previous life.

I suppose I should find out what the closing procedure is in this case, where there is no real solution.
0
 
LVL 42

Expert Comment

by:frodoman
ID: 16311532
Well, there is a solution - it is possible to do this in Crystal, it's just a whole lot of work.  I gave you the high-level algorythm that you'd use in a set of Crystal formulas.

I don't have the time to write the entire thing right now but if you want to attempt to work through it I'm happy to help.

Just FYI, I suspect the reason there is no Trimean function in Crystal is because it doesn't seem to be a standard function anywhere.  I did some checking on the internet and found at least three different implementations of it - some change the % discarded and some actually do a more weighted average by using the 25th, 50th, and 75th percentile and averaging them with double-weight on the 50th.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 16317869
A UFL will take no longer to write than the code in Crystal.  It can then be used in several reports.  I am not familiar with web delivery but, yes it would need to be included in the distribution.  Since you will be putting many dlls onto their server one more shouldn't be a problem.

How large are the datasets?  How much variance is there in the size?
By this I mean are the number of values typically 10-20 or 50-60 or 100 or is there a much wider range say some are 10 and others are 500.

mlmcc

0
 

Author Comment

by:Gryff
ID: 16319335
The site already has the normal crystal .dll's loaded onto the intranet (this was done years ago), incidently it occurs to me to mention that this is being written in crystal 8.5 rather than the latest version of crystal. Generally we just place the report into a folder on the webserver and then modify a web page to link to the file. I *think* there is some Javascript somewhere that loads all the crystal files, this is not a standard crystal web reports enviroment unfortunately.

The report allows for a date range so the datasets could be fairly small (one or two items per group) with a short date range on a quiet clients, to many hundreds per group (this would be less common but still possible) with a large date range on busy clients.

So assuming vb6, how would I take in the data for each detail line and then return a trimean (I can work out the logic for this part myself easily enough) in the group total. The UDF example seems to take data in and returns it straight away, I would need to make the function accept the figures on each detail line and at the end of the group return the trimean result.  

It seems that I could do most if not all of this with some formula's within crystal just as easily and without requiring the need for a custom .dll that we need to argue past security policies. In fact now that I have had some more sleep I see that Frodomans second paragraph was referring to that and not the UDF. oops.
0
 

Author Comment

by:Gryff
ID: 16319702
Ok, For future queries this is what I have done and it seems to have the desired results. Unfortunately it won't handle more than 1000 records per group, though hopefully this should be sufficient. This is ultimately what Frodoman suggested as an alternative to the UFL.

This Formula is in the detail line
Shared Values() as Double
shared aCount as Number
if isnull({SCCall.Call_DTime}) then
    Formula = ""
else
    aCount = aCount + 1
    Redim Preserve Values(aCount)
    Values(aCount) = {SCCall.Call_DTime}
    Formula = cstr(Values(aCount))
end if

This formula is place in the group Footer
Shared aCount as Number
if aCount > 0 then
    Shared Values(aCount) as Double
    dim i, j, tmp, discard
    For i = 1 To UBound(Values)
            For j = UBound(Values) To 1 + i Step -1
                    If Values(i) > Values(j) Then
                            tmp = Values(j)
                            Values(j) = Values(i)
                            Values(i) = tmp
                    End If
            Next
    Next
   
    If aCount > 2 then  
        Discard = int(aCount*.1)
        if Discard < 1 then discard = 1
    else
        Discard = 0
    end if
   
    dim Avg, Cnt as number
    Avg = 0
    for i = 1+Discard to UBound(Values)-Discard
        Avg = Avg + Values(i)
        Cnt = Cnt + 1
    next i
    aCount = 0
    Formula = avg/cnt
else
    Formula = ""
end if
0
 
LVL 42

Expert Comment

by:frodoman
ID: 16320276
Glad to help.

Incidentally, the 1000 limit does increase if you upgrade to a newer version of Crystal.  If you ever need more than 1,000 records you could always use two arrays and put all values below the median in one and the remainders in the other.

Cheers,

frodoman
0
 

Author Comment

by:Gryff
ID: 16320818
Thanks a Lot Frodoman,

I have done some tests and I hit the loop limits for my sorting before I hit the 1000 record limit. Just have to tell users to limit their reports to 6 months at a time or so.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question