| Author |
Message |
louisville2k10
Groupie
Joined: 13 Sep 2010
Location: United States
Online Status: Offline
Posts: 45
|
  
Topic: Summary not working
Posted: 14 Jan 2011 at 6:38am |
I am trying to summarize data, but when I do, it doesn't give me the correct sum or average. Has anyone had this issue before? If so, can you please help me figure out what might be going on?
Ex.
John 540
Sally 304
Jack 734
Sam 333
Will 444
Avg 607
Total 3034
|
|
Thanks for your help!
|
 IP Logged |
|
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 14 Jan 2011 at 9:04am |
How is the data displayed in the report? Is this in header or footer sections or is it in details sections? Also, you don't mention how your tables and links are set up.
I suspect you may be dealing with some "duplicate" records that aren't really duplicates but that show up because of the way your tables are joined.
-Dell
|
|
|
| IP Logged |
|
louisville2k10
Groupie
Joined: 13 Sep 2010
Location: United States
Online Status: Offline
Posts: 45
|
Posted: 07 Feb 2011 at 5:25pm |
I have data from 5 different tables, each linked by a common set of usernames and a year/month field. I have grouped it by username. My parameter is set to choose the usernames to be displayed and the year/month period I want the data to pertain to. The five fields are placed in the group header and then summed in the report footer. One of the five fields - average grade - is an average of ten data entries (each individual grade percentages). The average field is placed in the group header. The individual grades are placed in the details. The average of the average grades is placed in the report footer.
This is a sample of what I have:
Grp. hdr: Username | # of Days Attended | # of Days Missed | Avg. Grade
Details: Grades
Rep. footer Tot. # of Days Att. | Tot. # Days Missed | Overall Avg.
My totals are off. I'm thinking it might have to do with the Avg. grade calculation?! Please advise as soon as you can, as this is URGENT.
|
|
Thanks for your help!
|
| IP Logged |
|
louisville2k10
Groupie
Joined: 13 Sep 2010
Location: United States
Online Status: Offline
Posts: 45
|
Posted: 08 Feb 2011 at 2:19am |
Here are some photos, which I hope will help someone point me in the right direction...
|
|
Thanks for your help!
|
| IP Logged |
|
Keikoku
Senior Member
Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
|
Posted: 08 Feb 2011 at 2:40am |
|
Can't tell what might be the problem, but have you tried making a running total formula, toss it somewhere in the details, and look at the numbers record-by-record?
Essentially you're looking for whether the details are adding up correctly, whether it is the average formula that is wrong, or some other formula.
If the math is wrong, then you can look at where the math is going wrong.
|
| IP Logged |
|
louisville2k10
Groupie
Joined: 13 Sep 2010
Location: United States
Online Status: Offline
Posts: 45
|
Posted: 08 Feb 2011 at 3:00am |
That's a good tip. I think what might be happening is that some fields are being added multiple times when they should only be added once. How would I select the first instance of a record to be included in a summation and ignore the repeats.
For instance:
Manned Time QA
100 85
100 90
100 95
100 100
200 92
200 94
200 93
What I want is the total Manned Time to be 300, not 1000. But I want to average ALL of the QA scores.
|
|
Thanks for your help!
|
| IP Logged |
|
Keikoku
Senior Member
Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
|
Posted: 08 Feb 2011 at 3:18am |
There are many ways to do it. I used a running total, but I'm not sure if that will easily go with what you have (long formulas tend to lose me quickly).
Basically the running total checks each record sequentially, and if your records are ordered by Manned Time, then you can use the condition that says "if the field(s) in my current record is different from the field(s) in the previous record, include it"
So with a running total over Manned time you would sum over that field, and evaluate with formula
{manned_time} <> previous({manned_time})
However if it is not sorted by Manned time...that may be harder. Edited by Keikoku - 08 Feb 2011 at 3:19am
|
| IP Logged |
|
louisville2k10
Groupie
Joined: 13 Sep 2010
Location: United States
Online Status: Offline
Posts: 45
|
Posted: 08 Feb 2011 at 3:33am |
Keikoku, thanks for your help so far. I am getting warmer.
When I inserted a running total field for the manned time and asked for it to sum on the change of each username, the final running total gives me the correct amount. The problem is that I want to show this final amount (or ending running total number) on each page, rather than a running total.
Basically, I need to show a sum of manned time that only counts the first record of duplicated manned time fields for each user and present this same sum for each row of the user.
|
|
Thanks for your help!
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 08 Feb 2011 at 3:59am |
Group by users and set up the running total to reset for every user.
-Dell
|
|
|
| IP Logged |
|
Keikoku
Senior Member
Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
|
Posted: 08 Feb 2011 at 4:07am |
Basically, I need to show a sum of manned time that only counts the first record of duplicated manned time fields for each user and present this same sum for each row of the user.
I'm assuming you want something like
Manned Time QA
300 85
300 90
300 95
300 100
300 92
300 94
300 93
Running total in the details would do this I believe.
Not sure what you mean by displaying the final amount on each page.
So like if I take 200 users and add them all up, I'd have a huge number in the last page, but then you find out that the first page only shows the users that were added so far?
|
| IP Logged |
|
|
|
