Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Average of a running total Post Reply Post New Topic
Author Message
USSAmberJack
Newbie
Newbie
Avatar

Joined: 29 Jan 2013
Location: United States
Online Status: Offline
Posts: 2
Quote USSAmberJack Replybullet Topic: Average of a running total
    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!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Jan 2013 at 12:01pm
sum the original fields and divide by 7
IP IP Logged
USSAmberJack
Newbie
Newbie
Avatar

Joined: 29 Jan 2013
Location: United States
Online Status: Offline
Posts: 2
Quote USSAmberJack Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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
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.016 seconds.