Print Page | Close Window

Average of a running total

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=18972
Printed Date: 02 May 2024 at 11:15pm


Topic: Average of a running total
Posted By: USSAmberJack
Subject: Average of a running total
Date Posted: 29 Jan 2013 at 10:42am
I have a report that is grouped by CompletionID.  Each completion ID has a production record for each date.  I need to average the most recent 7 days of production greater than 0 for each completion.  I tried using a running total to limit the number of records for each completion ID to the 7 most recent, but I can't figure out how to average the production for only the most recent 7 dates.
 
 

Completion ID 2246                                    01/26/2013                                                                                             14.81

01/25/2013                                                                                               8.73
01/24/2013                                                                                             13.29
01/23/2013                                                                                             11.78
01/22/2013                                                                                             13.98
01/21/2013                                                                                             12.44 01/20/2013                                                                                             40.64

Completion Avg                                                                                       5.85

The completion average for the 7 dates selected should be 16.52, not 5.85.
 
Thanks in advance for your help!


Replies:
Posted By: DBlank
Date Posted: 29 Jan 2013 at 12:01pm
sum the original fields and divide by 7

Posted By: USSAmberJack
Date Posted: 30 Jan 2013 at 11:03am
Because I am using a running total and supressing the details for any record count greater than 7, when I sum the original fields it is giving me a sum for all records, not just the 7 I am displaying in my report.

Posted By: DBlank
Date Posted: 30 Jan 2013 at 11:31am
group on the date field (set to day)
insert a summary as the max of the date field at the group level
use the group sort and set it to top n and set n to 7
insert a running total as the sum of the field you want to get
make a formula fopr your average {#rt}/7


Print Page | Close Window