Sage 100

A request for all Crystal experts, please: a client would like me to subtotal GL Detail Posting dollars by week for the last 26 weeks, and then average those group totals AFTER excluding the maximum and minimum values to come up with, similar to the TRIMMEAN function in Excel. Is this even possible in Crystal? I cannot even create a 'maximum (sum (amount, week))' formula - it says that group subtotal cannot be summarized. Any suggestions would be appreciated!

It's been awhile since I delved in to Crystal but you might need to do a subreport with a shared public variable to pass the total back to the main report and use in a formula for your calculations. In the subreport you would calculate the maximum and the minimum and save them to the shared public variables which will then be used by the main report. Like I said ... it's been awhile so maybe someone else has a simpler, more elegant solution?

Well, I thought about that, but I can't pass in all 26 group subtotals... so that I can evaluate them later.

I'm rusty but I didn't think there was a limitation on passing variables from subreports. I must be missing something though.

Can't you send three summary fields (running total, min and max) to the group footer and then just perform the math (running total - min - max, divided by 24) in a formula?

I was thinking use Running Totals like Steve said.

Yes - that's much better than a sub-report.

I think the problem lies in the fact that Amber needs a Minimum and Maximum of the weekly totals not a min/max on each detail line and you can't do a min/Max on the weekly group summary. if that is the case, I would use a formula to calculate my own Min/max in the weekly group footer and then a running total like Steve says. Here is a quick formula I created for Maximum of just the credit amounts. . shared numbervar MyMax; if (MyMax<Sum ({GL_DetailPosting.CreditAmount}, {GL_DetailPosting.PostingDate}, ""weekly"")) then MyMax:= Sum ({GL_DetailPosting.CreditAmount}, {GL_DetailPosting.PostingDate}, ""weekly""); mymax The formula for the minimum is similar but you would first need to start your shared variable for the minimum to be a very high value so that it gets assigned with a value at the first week group footer.

My approach would be to create an array of 26 variables - [Wk01, Wk02, Wk03,...] one for the sum of each week. Then find the min and max within the array. Subtract the total of these two values from the sum of the entire array and divide by 24.

David - you are right about what I'm trying to accomplish. I can try your formulas. Jon, I think that's a fantastic idea! I've never used MakeArray, but I think I understand, from your notes, how to do it. Thank you everyone!