Joined: 04 Sep 2009
Location: United States
Online Status: Offline
Posts: 31
Topic: Grand Total Calc in Group Footer Posted: 04 Sep 2009 at 10:48am
I am trying desperatly to compare the average for a field for the whole data set to that in a group. I want toi use a grand total summary in a group footer. Any ideas?
Joined: 04 Sep 2009
Location: United States
Online Status: Offline
Posts: 31
Posted: 04 Sep 2009 at 11:21am
What I am trying to do is calcualte an average score per item if the socre was between 1 and 7. This has to be calculated per location so up until now I have been achiveing this by grouping. I need to compare the average per location to the overall average for that question. I was using a series of running totals so that I could exclude the scores I didn't want (8 and 9) but I can't use the average calculated from the running total for the WHOLE report to compare against each location because the grandtotal running total is only for data up to that point in the report.
Joined: 04 Sep 2009
Location: United States
Online Status: Offline
Posts: 31
Posted: 04 Sep 2009 at 12:33pm
I spoke to soon Because I put the exclusion of values of 8 or 9 in the select expert it is now exlcuding the whole record so the other questions in the survey are not being used. Is the a way for me to use NOT IN in my average? Some like:
AVERAGE(not ({ValYSSF_.F63} in [8,9]))
but this always gives me an error. Thanks for you help!
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 04 Sep 2009 at 1:21pm
Sorry, can't do that to my knowledge, hence the value of Rts.
Can you explain the data a little more, maybe a sample?
I don't understand why excluding the 8-9 is messing up the values. There may be a simple translation formula but I ned to understand the row level data.
Joined: 04 Sep 2009
Location: United States
Online Status: Offline
Posts: 31
Posted: 04 Sep 2009 at 1:40pm
The data source is terrible (from a survey done by the state of California). I think I may have gotten it to work by doing a home grown running total out of if then elses. I did an if then else to to calculate the sum (if {ValYSSF_.F63} >7 then 0 else {ValYSSF_.F63}) and then to get the count (if {ValYSSF_.F63} >7 then 0 else 1). I summed each of those fields and then divided one by the other. Problem is I have to do this for 25 fields!
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
IP Logged
Because I put the exclusion of values of 8 or 9 in the select expert it is now exlcuding the whole record so the other questions in the survey are not being used. Is the a way for me to use NOT IN in my average? Some like:
