Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Reportin services - AVG of avg Expand / Collapse
Author
Message
Posted Saturday, October 13, 2012 7:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 5:34 AM
Points: 5, Visits: 20
Hello,
I have question, how to sum rows. I create query =avg(iif(filds!urgency.value=1 and filds!_time<=14,1,iif(filds!urgency.value=2 and filds!_time<=130,1,0)), everything is ok, but I must sum average of average. When I sum row I get 93.33, but correctly score is 97,50%.



Maybe someone knows how to do it. How to sum calculated
Post #1372417
Posted Sunday, October 14, 2012 7:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 2:21 PM
Points: 132, Visits: 557
Hi,

I think 93.33% maybe the right answer because the average is being calculated in the scope of the whole year or whatever months (Jan-June) included.

While your expectation of the answer being 97.5 % is wrong because, its just the average of percentages of every month.

Just try to take the average of sales for each individual months (For Ex. sales/30) and then for whole year(sales/365) with some random values.


BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
Post #1372505
Posted Tuesday, October 16, 2012 5:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 5:34 AM
Points: 5, Visits: 20
But how to calculate the resulting field RS. As in the example above, after the calculation as a result I get indirect outcome which I want to sum them up.
Is this possible?
Post #1373134
Posted Tuesday, October 16, 2012 10:06 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:58 AM
Points: 97, Visits: 355
wait, I don't understand. You want to sum the up percentages? If you are summing up the percentages, then it would be 487.5%. Or you are looking for (100%+100%+100%+100+87.5%)/5 = 97.5%?

------------
:)
Post #1373380
Posted Tuesday, October 16, 2012 10:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 5:34 AM
Points: 5, Visits: 20
Yes, I'm looking for answers to this (100%+100%+100%+100+87.5%)/5 = 97.5%?
Post #1373399
Posted Wednesday, October 17, 2012 12:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 2:21 PM
Points: 132, Visits: 557
You can use the following expression to get the sum of percentages:

=REPORTITEMS!Textbox15.Value

where 'Textbox15' is the textbox in which you are showing the percentage.

you have to get the value of '5' from somewhere...



BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
Post #1373649
Posted Wednesday, October 17, 2012 8:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:03 AM
Points: 203, Visits: 817
The simple school maths level answer

Never average averages.

The slightly better answer

Never average averages unless the sample sizes of all elements are identical

The reason

suppose you have a set of average speeds for traffic along a road
January 30mph
February 45mph
March 15 mph

What is the average speed for the quarter
You cannot tell as you do not have sufficient information - you might think its (30+45+15)/3 = 30
That is highly unlikely to be correct - for two reasons
1 you don't know the sample sizes
2 speeds are ratios

Now suppose you know that you have
January
Vehicle a 10 miles at 30 mph
Vehicle b 5 miles at 30 mph
vehicle c 25 miles at 30 mph

February
Vehicle d 10 miles at 50 mph
Vehicle e 10 miles at 40 mph


March
vehicle f 25 miles at 15 mph


Total distance travelled = 85 miles
Total time taken (10/30)+(5/30)+(25/30)+(10/50)+(10/40)+(25/15)=
3.45 hours

Average speed= 85/3.45

=~ 24.64 mph


Post #1373832
Posted Monday, October 22, 2012 2:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 5:34 AM
Points: 5, Visits: 20
crmitchell, I agree with you but my supervisor won't avg avg
SSC-Enthusiastic, Your solution"=REPORTITEMS!Textbox15.Value", gives last calculated value, but I want sum in this table.

When I "=sum(REPORTITEMS!Textbox15.Value) I get error messages.
Post #1375299
Posted Wednesday, October 24, 2012 6:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 2:21 PM
Points: 132, Visits: 557
You may need to convert the values into numbers before adding them up.

=SUM(CAST(ReportItems!Textbox15.Value AS INT) )


BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
Post #1376425
Posted Friday, November 9, 2012 3:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 5:34 AM
Points: 5, Visits: 20
Hi,

When I used =SUM(CAST(ReportItems!Textbox13.Value AS INT) ) I get the message "The Value expression for the textrun 'sum.Paragraphs[0].TextRuns[0]' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers".
Post #1382914
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse