Moving Average
Can any one be able to help me to write a querry on Moving Average
Example
Product Volume Moving Average Working
Fish 300 300 300 /1
Fish 100 200 (300 + 100) /2
Fish 500 300 (300 + 100 + 500) /3
Fish 100 200 (300 + 100 + 500 + 200 ) /4
Thanks
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Moving Average
Hi All, I am trying to create a report that shows moving average. Well I found this template: // /*Returns the average value of a member over a specified time interval.*/ CREATE MEMBER CURRENTCUBE.[MEASURES].[Moving Average] AS Avg ( [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember.Lag(<<Periods to Lag>>) : [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember, [Measures].[<<Target Measure>>] ) // This calculation returns the average value of a member over the specified time interval. , FORMAT_STRING = "Standard"; Only I do not have a specified interal or a constant number that I can place into .Lag(<<Periods to Lag>>) The report I have has a parameter that allows you to select a specific team project from a list. Although I have not figured out how to get the iteration paramter to update to only show the specified team project iterations, I want the graph to only show the iterations for that team project and the moving average (in the graph). The problem is that I can't just plug in the number 6 because different projects we have, have different numbers of iterations, and the user can pick any project from the list in the parameter. Does anyone have suggestions on this? Thanks! -Nici
View Replies !
View Related
Moving Average
Can anyone tell me how to calculate a moving average for the below table? -------------------- MONS /vol /counter(the date) jan/8 / 2006-01-01 feb/9 / etc. ........ oct/5 / etc. nov/6 / dec/7 / --------------------- I am using the below code, but it is having no effect...... SELECT i1.MONS, i1.COUNTER, ( SELECT AVG( i2.VOL ) FROM #RES1 AS i2 WHERE i2.MONS = i1.MONS And DATEDIFF( month , i2.COUNTER , i1.COUNTER ) Between 0 And 12 ) AS MovingAverageOver12Months FROM #RES1 AS i1 ORDER BY i1.COUNTER thanks in advance, dirwin
View Replies !
View Related
SSRS2K5 Moving Average
Hello Guys, Im quite new to SSRS and SQL in general and i've found this site to be a goldmine, and I found a thread on here relating to moving averages but I don't think it meets my needs. I have a simple bar chart in SSRS with a Category Field (Fields!WeekYear.Value) which has the week number and year value on the axis, and a Data Field (Fields!Amount.Value) which is just a quantity for the given week. Now what I need is a Moving Average of the amount with each new week generated (Simple and 3-point and/or 5-point), I've tried using some code and even functions but nothing is working for me so I was wondering if anyone here can help me out? PS I've been tinkering with the RunningValue function and my DataSet is called "Cube" so I assumed that the scope should be "Cube" aswell but it only returns an error, am I right or am I being retarded? As I say I am very new so please be gentle with me :) Thank You in advance. Man im a n00b!
View Replies !
View Related
Problem Calculating Moving Average
Firstly, sorry for the long post, but I've included a fair bit ofsample data.Im doing a comparision of 10yr Bond prices and CPI adjustments, withan 18 week moving average of the CPI. I'm using a nested sub-query tocalculate the moving average, but I'm having difficulty selectingexactly 18 data points (ie When I include the 'HAVING COUNT(C1.Closes)= 18' line, I get no results).Can anyone help?-- Some sample data:CREATE TABLE Bond10 (Closes [datetime] NOT NULL ,Prices [smallmoney] NOT NULL ,)INSERT INTO Bond10SELECT '1994-01-14', 6.57 UNIONSELECT '1994-01-21', 6.53 UNIONSELECT '1994-01-28', 6.44 UNIONSELECT '1994-02-04', 6.51 UNIONSELECT '1994-02-11', 6.54 UNIONSELECT '1994-02-18', 6.89 UNIONSELECT '1994-02-25', 7.18 UNIONSELECT '1994-03-04', 7.43 UNIONSELECT '1994-03-11', 7.43 UNIONSELECT '1994-03-18', 7.44 UNIONSELECT '1994-03-25', 7.66 UNIONSELECT '1994-04-01', 7.96 UNIONSELECT '1994-04-08', 8.07 UNIONSELECT '1994-04-15', 8.24 UNIONSELECT '1994-04-22', 8.23 UNIONSELECT '1994-04-29', 8.45 UNIONSELECT '1994-05-06', 8.82 UNIONSELECT '1994-05-13', 8.86 UNIONSELECT '1994-05-20', 8.44 UNIONSELECT '1994-05-27', 8.75 UNIONSELECT '1994-06-03', 8.79 UNIONSELECT '1994-06-10', 8.77 UNIONSELECT '1994-06-17', 9.24 UNIONSELECT '1994-06-24', 9.63 UNIONSELECT '1994-07-01', 9.66 UNIONSELECT '1994-07-08', 9.59 UNIONSELECT '1994-07-15', 9.41 UNIONSELECT '1994-07-22', 9.56 UNIONSELECT '1994-07-29', 9.58 UNIONSELECT '1994-08-05', 9.31CREATE TABLE AvgCPI (Closes [datetime] NOT NULL ,AvgCPI [smallmoney] NOT NULL ,)INSERT INTO AvgCPISELECT '1994-01-14', 2.04 UNIONSELECT '1994-01-21', 2.04 UNIONSELECT '1994-01-28', 2.04 UNIONSELECT '1994-02-04', 2.04 UNIONSELECT '1994-02-11', 2.04 UNIONSELECT '1994-02-18', 2.04 UNIONSELECT '1994-02-25', 2.04 UNIONSELECT '1994-03-04', 1.51 UNIONSELECT '1994-03-11', 1.51 UNIONSELECT '1994-03-18', 1.51 UNIONSELECT '1994-03-25', 1.51 UNIONSELECT '1994-04-01', 1.51 UNIONSELECT '1994-04-08', 1.51 UNIONSELECT '1994-04-15', 1.51 UNIONSELECT '1994-04-22', 1.51 UNIONSELECT '1994-04-29', 1.51 UNIONSELECT '1994-05-06', 1.51 UNIONSELECT '1994-05-13', 1.51 UNIONSELECT '1994-05-20', 1.51 UNIONSELECT '1994-05-27', 1.51 UNIONSELECT '1994-06-03', 1.80 UNIONSELECT '1994-06-10', 1.80 UNIONSELECT '1994-06-17', 1.80 UNIONSELECT '1994-06-24', 1.80 UNIONSELECT '1994-07-01', 1.80 UNIONSELECT '1994-07-08', 1.80 UNIONSELECT '1994-07-15', 1.80 UNIONSELECT '1994-07-22', 1.80 UNIONSELECT '1994-07-29', 1.80 UNIONSELECT '1994-08-05', 1.80-- My query so far:SELECT A1.Closes, A1.Prices, B1.AvgCPI, SUM(C1.AvgCPI) AS MovSumCPI,AVG(C1.AvgCPI) AS MovAvgCPI, COUNT(C1.AvgCPI) AS CounterFROM (SELECT Closes, Prices FROM Bond10) A1LEFT JOIN (SELECT Closes, AvgCPI FROM AvgCPI) B1 ON A1.Closes = B1.ClosesLEFT JOIN (SELECT Closes, AvgCPI FROM AvgCPI) C1 ON C1.Closes >= A1.Closes AND DATEADD(Week,-18,C1.Closes) <A1.ClosesGROUP BY A1.Closes, A1.Prices, B1.AvgCPI, C1.AvgCPI-- HAVING COUNT(C1.Closes) = 18ORDER BY A1.ClosesDROP TABLE Bond10DROP TABLE AvgCPIExpected ResultsCloses Bon10 AvCPI MovAvg========== ==== ==== ====14-Jan-94 6.57 2.0414-Jan-94 6.57 2.0421-Jan-94 6.53 2.0421-Jan-94 6.53 2.0428-Jan-94 6.44 2.0428-Jan-94 6.44 2.044-Feb-94 6.51 2.044-Feb-94 6.51 2.044-Feb-94 6.51 2.0411-Feb-94 6.54 2.0411-Feb-94 6.54 2.0411-Feb-94 6.54 2.0418-Feb-94 6.89 2.0418-Feb-94 6.89 2.0418-Feb-94 6.89 2.0425-Feb-94 7.18 2.0425-Feb-94 7.18 2.0425-Feb-94 7.18 2.04 2.044-Mar-94 7.43 1.51 2.014-Mar-94 7.43 1.51 1.9811-Mar-94 7.43 1.51 1.9511-Mar-94 7.43 1.51 1.9218-Mar-94 7.44 1.51 1.8918-Mar-94 7.44 1.51 1.8625-Mar-94 7.66 1.51 1.8325-Mar-94 7.66 1.51 1.801-Apr-94 7.96 1.51 1.781-Apr-94 7.96 1.51 1.758-Apr-94 8.07 1.51 1.728-Apr-94 8.07 1.51 1.6915-Apr-94 8.24 1.51 1.6615-Apr-94 8.24 1.51 1.6322-Apr-94 8.23 1.51 1.6022-Apr-94 8.23 1.51 1.5729-Apr-94 8.45 1.51 1.5429-Apr-94 8.45 1.51 1.516-May-94 8.82 1.51 1.516-May-94 8.82 1.51 1.5113-May-94 8.86 1.51 1.5113-May-94 8.86 1.51 1.5120-May-94 8.44 1.51 1.5120-May-94 8.44 1.51 1.5127-May-94 8.75 1.51 1.5127-May-94 8.75 1.51 1.513-Jun-94 8.79 1.8 1.5310-Jun-94 8.77 1.8 1.5417-Jun-94 9.24 1.8 1.5624-Jun-94 9.63 1.8 1.571-Jul-94 9.66 1.8 1.598-Jul-94 9.59 1.8 1.6115-Jul-94 9.41 1.8 1.6222-Jul-94 9.56 1.8 1.6429-Jul-94 9.58 1.8 1.665-Aug-94 9.31 1.8 1.67Thanks,Stephen
View Replies !
View Related
Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE) 1 01/01/2007 PURCHASE 10 1000 2 01/01/2007 PURCHAES 5 1100 3 01/01/2007 SALES -5 *TobeCalculated 4 02/01/2007 Purchase 20 9000 5 02/01/2007 SALES -10 *TobeCalculated 5 02/01/2007 purchase 50 8000 6 03/01/2007 Sales -10 *TobeCalculate 7 01/01/2007 Purchase 20 12000 I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here) In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly. When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly. The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated. Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time. Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
View Replies !
View Related
User-Defined Functions To Calculate Average And Relative Percent Difference(RPD):How To Designate The Variables Of Average/RPD
Hi all, This is my first time to do T-SQL User-Defined Functions programming. I just learned some basic DDL and DML statements. But I do not know how to designate the variables of the User-Defined Function. I have 3 dbo tables in SQL Server Management Studio Express (SSMSE): dbo.Projects: ProjectID ProjectName LabName 1 Blueriver ALSI 2 Greentree GPL 3 Redrock STL-NJ NULL NULL NULL dbo.Samples: SampleID SampleName Matrix SampleType Chemical ProjectID 1 Blueriver01 Water Primary VOCs 1 2 Blueriver02 Water Duplicate VOCs 1 3 Blueriverr03 Water QA VOCs 1 4 Greentree11 Soil Primary VOCs 2 5 Greentree12 Soil Duplicate VOCs 2 6 Greentree13 Soil QA VOCs 2 NULL NULL NULL NULL NULL NULL dbo.LabTests: AnalyteID AnalyteName Result Unit SampleID 1 Acetone 120.80 ug/L 1 2 Benzene 25.60 ug/L 1 3 Trichloroethene 13.00 ug/L 1 4 Xylenes 0.00 ug/L 1 5 Acetone 90.70 ug/L 2 6 Benzene 31.40 ug/L 2 7 Trichloroethene 19.20 ug/L 2 8 Xylenes 2.00 ug/L 2 9 Acetone 140.30 ug/L 3 10 Benzene 21.50 ug/L 3 11 Trichloroethene 22.20 ug/L 3 12 Xylenes 0.00 ug/L 3 13 Acetone 222.10 ug/Kg 4 14 Benzene 10.30 ug/Kg 4 15 Trichloroethene 30.20 ug/Kg 4 16 Xylenes 50.70 ug/Kg 4 17 Acetone 211.90 ug/Kg 5 18 Benzene 16.40 ug/Kg 5 19 Trichloroethene 34.70 ug/Kg 5 20 Xylenes 60.00 ug/Kg 5 21 Acetone 220.30 ug/Kg 6 22 Benzene 13.20 ug/Kg 6 23 Trichloroethene 32.00 ug/Kg 6 24 Xylenes 55.50 ug/Kg 6 NULL NULL NULL NULL NULL The Average of chemical/analyte in 2 samples is defined as: Average = Abs(the result of primary sample + the result of duplicate sample)/2. Average = Abs(the result of primary sample + the result of QA sample)/2. Average = Abs(the result of duplicate sample + the result of QA sample)/2. The RPD of chemical/analyte in 2 samples is defined as: RPD = Abs(the result of primary sample - the result of duplicate sample)/ Average RPD = Abs(the result of primary sample - the result of QA sample)/ Average RPD = Abs(the result of Duplicate sample - the result of QA sample)/ Average I want to calculate the average and RPD of each chemical/analyte for the following 3 pairs of the related samples: (i) Between the primary and duplucate samples (ii) Between the primary and QA samples (iii) Between the duplucate and QA samples. In the Table "dbo.Projects", ProjectID is the primary key. In the Table "dbo.Samples", SampleID is the primary key and ProjectID is the foreign key. In the Table "dbo.LabTests", AnalyteID is the primary key and SampleID is the foreign key. I do not know how to designate the variable of each result of analyte in a sample and use it to calucalte the Average and RPD for each pair (i.e. for (i), (ii), or (iii)). For example: I do not know how to designate the following 2 results: dbo.LabTests: AnalyteID AnalyteName Result Unit SampleID 1 Acetone 120.80 ug/L 1 5 Acetone 90.70 ug/L 2 Please help and advise me how to designate the 2 results in T-SQL User Defined Functions to calculate the Average and RPD. Thanks in advance, Scott Chang
View Replies !
View Related
Latches - Average Waits, Average Duration?
I have been monitoring the average number of latch waits and the average duration of each latch wait on my primary SQL box. On average, I see around 30 latch waits per second with an average duration of under 1 second. What type of average are you seeing on your production machines? What is a normal average? I have tried researching for suggested values on these two counters, but I haven't turned up any information. Thanks, MV
View Replies !
View Related
Need An Average By Year Of An Average By Month
I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged... I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25. The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement. ?confused? Here's the original query. accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): ' SELECT name, country_cd from weather_station where platformId=&&platformId; SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS FROM daily WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime GROUP BY to_char(datetime,'MM') ORDER BY to_char(datetime,'MM'); with a result of: NAME_________________CO -------------------- -- OFFUTT AFB___________US MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS -- ---------- ---------- ---------- ----------- 01_________21______-5.31________-30__________60 02_________26______-2.19______-28.3__________61 03_______31.1_______3.61______-26.1__________60 04_______35.6______11.07______-12.2__________60 05_______37.2_______17.2_______-3.3__________60 06_______41.1______22.44__________5__________60 07_______43.3______24.92________7.2__________60 08_______40.6______23.71________5.6__________60 09_________40______18.84_______-2.2__________59 10_______34.4_______12.5_______-8.9__________59 11_________29_______4.13______-23.9__________60 12_________21______-2.52______-28.3__________60
View Replies !
View Related
Average
Hello,I have two tables:[Posts] > PostId, ...[Ratings] > RatingId, PostId, RatingI want to select all posts and add a new column named AverageRating.This new column is the average of all ratings associated to that Post.If a post was not rated then its AverageRating would be NULL.How can I do this?Thanks,Miguel
View Replies !
View Related
Average Help
if I have a bunch of times, and want an average of them, how do I do that? Thanks. For more details, see this thread: http://www.dbforums.com/showthread.php?t=1215633
View Replies !
View Related
Average
Hi All, I was hoping someone could help me with pluging moving average into my report. This is the template I am using for moving average: // /*Returns the average value of a member over a specified time interval.*/ CREATE MEMBER CURRENTCUBE.[MEASURES].[Moving Average] AS Avg ( [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember.Lag(<<Periods to Lag>>) : [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember, [Measures].[<<Target Measure>>] ) // This calculation returns the average value of a member over the specified time interval. FORMAT_STRING = "Standard"; This is what I have before I started to add the moving average. The report works but only displays the cummulative complexity rank. WITH MEMBER [Measures].[Date Key] AS [Date].[Date].CurrentMember.UniqueName SELECT { [Measures].[Date Key], [Measures].[ComplexityRank] } ON COLUMNS, ( [Work Item].[System_State].[System_State], (StrToMember(@StartDateParam):StrToMember(@EndDateParam)) ) ON ROWS FROM [Team System] WHERE ( STRTOMEMBER("[Team Project].[Team Project].["+@Project+"]"), STRTOSET(@IterationParam), STRTOSET(@AreaParam), STRTOSET(@WorkItemTypeParam) ) What I want to do is also add the moving average for this over the parameter of Iterations. (IterationParam). This is what I have so far with the moving average in my report: I know I must have a lot of errors but I can't get past this first error. I highlighted the line with error. The error said: Query(7, 15) Parser: The syntax for '.' is incorrect. (msmgdsrv) WITH MEMBER [Measures].[Date Key] AS [Date].[Date].CurrentMember.UniqueName, [Measures].[Moving Average] as AVG({[Date].[Date].currentmember.lag(@IterationParam): [Date].[Date].currentmember},[Measures].[ComplexityRank]) SELECT { {measures.[moving average],measures.[Internet Total Product Cost]} on 0, [Date].[Date]. Members [Measures].[Date Key], [Measures].[ComplexityRank] } ON COLUMNS, ( [Work Item].[System_State].[System_State], (StrToMember(@StartDateParam):StrToMember(@EndDateParam)) ) ON ROWS FROM [Team System] WHERE ( STRTOMEMBER("[Team Project].[Team Project].["+@Project+"]"), STRTOSET(@IterationParam), STRTOSET(@AreaParam), STRTOSET(@WorkItemTypeParam) Does anyone have any suggestions? Thanks, Nici
View Replies !
View Related
How To Get Average Of Average.
Hi ! I am having this problem. I have a grouping someting like this. Group1 Resolved Resolution Time Average Resolution Time 30 2904.46 96.82 54 1442.03 26.70 0 0.00 0.00 0 0.00 0.00 ------------------------------ Avg : 30.88 Now to calculate the average inside the grouping I have used this iif(Sum(Fields!ResolutionTime.Value) = 0 ,"0.00",(Sum(Fields!ResolutionTime.Value)/(iif(Count(Fields!ResolvedDate.Value)=0,1,Count(Fields!ResolvedDate.Value))))) I am at a loss how to calculate the group footer (Result = 30.88 ) which is equal to (96.82 + 26.70 + 0 + 0 ) / 4 . This is the result from the existing Crystal report. I am trying to convert this report to reporting services. Now I cannot nest aggregate functions and also the row number can be dynamic I am confused how to resolve it. Any ideas will be greatly appreciated. Thanks in advance. --Abbi.
View Replies !
View Related
Getting Average?
Using MS SQL 2005 server how can I get the average cost price of item 'shirt' from this table please? code item description cost retail ---- ---- ----------- ---- ------ 191 shirt shirt - white 2.30 9.99 170 pants pants - beige 6.34 15.00 196 shirt shirt - red 2.64 9.99 199 shirt shirt - blue 2.61 9.99
View Replies !
View Related
Average?
I am getting the number of transactions for two different months based on dates entered from the user. I need to display the number of transactions along with the average sale. I have figured out how to get the dates and sum the transactions but I can't seem to add in the average.... (@prevMonthStart datetime, @prevMonthEnd datetime, @thisMonthStart datetime, @thisMonthEnd datetime) as select sum(case when orders.orderdate between @prevMonthStart and @prevMonthEnd then 1 else 0 end ) as PrevMonthCount, sum(case when orders.orderdate between @thisMonthStart and @thisMonthEnd then 1 else 0 end ) as ThisMonthCount (how do I add in the average for both months? The column is orders.ordertotal)
View Replies !
View Related
Getting An Average From A Table
I have two tables, table a holds all the votes by users of each element in table b. I was wondering, how do I get the average of all those votes in table a that relate to that each instance of a element in table b. For example table b has two elements created by a certain user that has been voted 5 times each by users with scores like 2, 5, 4, 2 , 2 for both of them. I just need to get the average of those numbers that pertain to those elements in table b. Thanks for any help.
View Replies !
View Related
How To Get Weekly Average?
I have a view call view_jobComponent and it contain the following fields: date_complete, job_tag_no, part_model, component_id, component_description, component_qty If I want to get the average component_qty in a certain time frame (weekly, monthly, qtrly, bi-annually, annually .etc) in a certain time period. For example, I want to get the weekly average of component_qty for the year of 2005, how should my sql query like?
View Replies !
View Related
Hourly Average
Apologies for the simplicity of the question, but it reflects mycapabilities! I have the following sample fields coming from differenttables:LocationTimeDate (timestamp)DataI need to return the average of Data per Location per HOUR.Thanks.
View Replies !
View Related
How To Do An Average Of A Count
Hi AllWe have an order processing database which includes the standard OrderHeader/Order Lines tables. I'm trying to write a query to get the averagenumber of lines per order over a given period, and I'm stuck :-(I can get the number of lines per order with:SELECT COUNT(*) FROM OrderDetailsINNER JOIN Order Header ONOrderHeader.OrderNo = OrderDetails.OrderNoWHERE OrderHeader.OrderDate ..... {various criteria} ...GROUP BY OrderDetails.OrderNumberBut how do I then get an average of this for the period?TIAMike Bannon
View Replies !
View Related
Average Of Middle 90%
Hello all!This might be a newbie question, and there might be something Im just not thinking of right now, but I have a set of values that I need to get the average of but only from the middle 90%. Example:11 <-From here1234456 <- To here.7I thought I could solve it by subqueries and do the following:Select (((Select sum top 5 order asc) + (Select sum top 5 order desc)) - sum total)/rows*0.9 which would give me what I want, but I realised that when aggregating I cant order the subqueries.This is for an application (that will run the query on a sql-server) that only takes one query (although subqueries should be fine), and thats why I have a problem, I cant build any views or things like that.I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?
View Replies !
View Related
MDX Average Query
Hi I have been batteling to get the below right. I am fairly new to MDX but managing to keep my head above water. I am trying to get the average of the Due vs Delivery. What makes this more interesting is that not all the items have been delivered. Here is some sample information from the underlying fact table. Department Size Due Date Delivery Date Due vs Delivery Count 01-Mens 0028 13/04/2006 13/04/2006 0 1 01-Mens 0028 13/04/2006 13/04/2006 0 1 01-Mens 0028 13/04/2006 NULL NULL 1 01-Mens 0028 13/04/2006 NULL NULL 1 01-Mens 0028 13/04/2006 NULL NULL 1 01-Mens 0028 18/04/2006 NULL NULL 1 01-Mens 0028 13/04/2006 12/04/2006 -1 1 01-Mens 0028 13/04/2006 12/04/2006 -1 1 01-Mens 0028 13/04/2006 12/04/2006 -1 1 01-Mens 0028 13/04/2006 12/04/2006 -1 1 I am trying to get to an answer where MDX sums the Due vs Delivery and Devides it by the lines that actualy contains some value and not null. The answer should come out to be -4/6 = -0.67 Any help would be much appreciated
View Replies !
View Related
Compute Average
Hello, I have columns in a table and each column may have a value of 0, 1 or NULL. I see that I am going to have trouble with columns that are NULL. I do not want to use a column if it is NULL as my number of total columns to divide the sum of the totals by. For example: Col1 Col2 Col3 Col4 0 1 NULL 1 I would want to add Col1, Col2, and COl4 (for a total of 2) together and then divide by 3 (the number of columns that are not NULL). How could I do something like this? Thanks for your help. I have been lost for a solution.
View Replies !
View Related
Distinct Value Or Average
I don't even know if this is possible but here is a picture of some sample results. Description Amount Account Location Begin Balance Test 1234 $100 0001 Las Vegas $5000 Test 2345 $50 0001 Las Vegas $5000 Test 3456 $80 0001 Phoenix $2000 What this is, is transactions and their amounts and account/location they went to. The last column is the beginning balance for the account/location combination. I only need the beginning balance once per account/location or averaged. So I need either this (beginning balance only shows once)..... Description Amount Account Location Begin Balance Test 1234 $100 0001 Las Vegas $5000 Test 2345 $50 0001 Las Vegas $0 Test 3456 $80 0001 Phoenix $2000 or I need this (beginning balance is averaged)...... Description Amount Account Location Begin Balance Test 1234 $100 0001 Las Vegas $2500 Test 2345 $50 0001 Las Vegas $2500 Test 3456 $80 0001 Phoenix $2000 That way I can do a sum on the beginning balance results in my report for the account/location combination, and do a sum on just account or just location while still having my transactions. Is this possible? Thanks, Adam
View Replies !
View Related
Average Of All Records Bar 1
I have a table that always contains 13 records. It contains a column called 'RecordKey' and a colum called 'ResTime' I need to get the average of the 12 Restimes with the highest record Key (ie. Average of all in the table except the bottom RecordKey), then round that result to 2 decimal places. Ive been trying variations of this statment: set @AverageResult = round((SELECT AVG(Select Top(RecordKey, 12) from Reports_PI)ResTime) FROM Reports_PI),2) I cant seem to get it to work. Any ideas what im doing wrong?
View Replies !
View Related
Average In Matrix
Just want to gathr some ideas.I have a matrix where i retrieve Fields!Amount.I used the subtotal property to get the total amount per quarter.But how can i get the average of the 3 months.I tried using the Avg(Fields!Amount.Value) but it's results is unexpected or wrong. ________________________ | Avg(Fields!Amount.value) | | Jan | Feb | March | ------------------------------------------- | 0.1 | 0.2 | 0.2 | | 0.3 | 0.1 | 0.5 | SubTotal | 0.4 | 0.3 | 0.7 | That is the design of my matrix.And other thing,it is dynamic,for example,There is a possibility that Feb could be absent.SO i cannot make Sum(Fields!Amount.Value) / 3. Dont know what to do for that.Why could Avg be wrong, correct me if im wrong,but as for what i know,i think the formula for Avg in my matrix is : Avg = Sum of 3 totals / no. of months.. Hope to hear from anyone out there..Thanks!!
View Replies !
View Related
Getting Every Other 5-minute Average Value...
Hi, Here is a part of result set. It is of every minute value. How can I get every other 5-minute average value? id datetime value ------------------- ----------------------------- -------- 0xC00302FD 2008-01-31 18:36:00 0.104 0xC00302FD 2008-01-31 18:37:00 0.104 0xC00302FD 2008-01-31 18:38:00 0.104 0xC00302FD 2008-01-31 18:39:00 0.104 0xC00302FD 2008-01-31 18:40:00 0.104 0xC00302FD 2008-01-31 18:41:00 0.104 0xC00302FD 2008-01-31 18:42:00 0.104 ... ... ...
View Replies !
View Related
Average With Specified Output
time_stamp met_id ch1avg 12/7/2006 12:00:00 AM 0970 .5 12/7/2006 12:10:00 AM 0970 1.10 I have a table which collects data at 10 minute intervals, so per day, per met_id there are 144 records. Averaging ch1avg based on a single date query correctly produces 144 records. I am trying produce 144 record average based on any date spread in the where clasue. For example if the where is time_stamp between '01/01/2007' and '01/03/2007' this would be 288 records however the ouput needs to always be grouped into 144 records maximum. Looking for any assistance.
View Replies !
View Related
Average Or Gorup By
hi guys I have a list of data, but I want to calculate the average. for example dh from depth bi a 0 1 4.5 a 1 2 3.2 a 2 5 8.2 a 5 7 10.5 a 7 10 5 a 10 15 7.8 a 15 20 9.5 a 20 22 10.2 b 0 5 2.3 b 5 7 3.1 b 7 9 4.6 b 9 12.5 12 b 12.5 15 5.5 b 15 17 8.9 b 17 19 10 this means that for each interval I have a value in the field bi and I want to calculate an average for the total for each dh something like this for a group of values dh bi a ( (depth-from)* bi)/sum(depth-from) b ((depth-from)* bi)/sum(depth-from) thanks for your help
View Replies !
View Related
Help With Average Check
Hello Sql Expert! I am trying to create a query that i can use to find the Average Check for transactions sold with a particular product. The sql table i'm working with looks like this ORDERID PRODUCT PRICE COUNT PAID 00001 burger 1.00 1 1.00 00001 hotdog 2.00 2 4.00 00001 drink 1.00 1 1.00 00001 shake 2.00 1 2.00 00002 burger 1.00 2 2.00 00002 shake 2.00 5 10.00 00003 hotdog 2.00 1 2.00 I would like to create something that will give me the average check for every product ex PRODUCT AVG CHECK burger 10 shake 10 hotdog 5 drink 8 I created a query that could give me the % of each product that are ordered alone. I tried modifying it to give me avg check but ran into problems because the when i use max(product) to aggergate the field, it uses the products with the highest first character in the alphabet thus it products with V's and Y's get a lot more results ( Is there an easy way to do this by creating a temp table or nested query? I can create a nested query to give me the average check for only one product at a time. I'm looking for something that can group the average check by all product. Any help would be greatly appreciated! Thank you so much!
View Replies !
View Related
Finding Average
Hi, I have two tables, Weekly and AverageEngTime. Weekly table has 14 columns, "Mon_Day,Mon_Night,Tue_day,....Sun_Night". AverageEngTime table has 15 columns, "Shifts,Mon_Day,Mon_Night,Tue_day,....Sun_Night". I have inserted "Average" as a value for column "Shifts" in AverageEngTime table. Now how do I find the average for each column in Weekly table and insert into AverageEngTime table in the respective columns, and in the row where Shifts = "Average". I used the following codes to try but i receive errors at where statement.Please correct me. The expected output as below. Shifts | Mon_day | Mon_Night | Tue_Day..... Average | 5.2 | 10.2 | 15.2........ The codes that i tried.. declare @Weekday tinyint, @hour int select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate()) if (@Weekday= 1 and (@hour>= 7 AND @hour<19)) begin Insert Into AverageEngTime(Sat_Night) where Shifts = 'Average' SELECT AVG(Sat_Night) FROM Weekly end if ((@Weekday= 1 and @hour >= 19) OR (@Weekday = 2 and @hour < 7)) begin Insert Into AverageEngTime(Sun_Day) where Shifts = 'Average' SELECT AVG(Sun_Day) FROM Weekly end
View Replies !
View Related
Average By Columns
Here's my sample formula: SELECT dbo.TechphonesCalibQA.ID, dbo.TechphonesCalibQA.EmpID, dbo.TechphonesCalibQA.EmpName, dbo.TechphonesCalibQA.[Level], CASE dbo.TechphonesCalibQA.G_Branded WHEN dbo.TechphonesCalibClient.G_Branded THEN 1 ELSE 0 END AS G_Branded FROM dbo.TechPhonesCalibClient INNER JOIN dbo.TechphonesCalibQA ON dbo.TechPhonesCalibClient.SesID = dbo.TechphonesCalibQA.SesID I would like to get the average of column G_branded (Result from the above formula) Any idea? Thanks
View Replies !
View Related
How Can I Get The Average Datediff?
I made a code to in SQL reporting services for the reports of our company regarding the new applicants. Now I need to get the average datedifference in each phase from all applicants who entered Initial/Data Collection. Example, "Initial/Data collection"PHASE will have an average of 60 minutes to be finished, averaged from all 200 applicants, and "Screening"PHASE 240 minutes to be finished, averaged from 100 applicants. All applicants must go through stage 1 before he can go to stage 2 and so on. The date diff will be obtained from the "createdon" column subtracted to the last "modifiedon" column in each phase. I need to insert that code to the existing code that I have if it is possible(which my boss prefer); or either if i can manipulate it in SQL reporting services in visual studio 2005. Here is a sample output that I have.Each phase has a different color. Here is the code that i used SELECT subject, regardingobjectidname, createdon, modifiedon, stat, phase, initial, (CASEphaseWHEN 'Initial/Data Collection' THEN 1 WHEN 'Screening' THEN 2 WHEN 'Assesment and Selection' THEN 3 WHEN 'Placement' THEN 4 END) AS Phasesort FROM( SELECT subject, regardingobjectidname, createdon, modifiedon, stat, initial, (CASEstatWHEN 'Application Recieved' THEN 'Initial/Data Collection' WHEN 'For Screening' THEN 'Screening' WHEN 'Shortlisted' THEN 'Screening' WHEN 'For Assesment' THEN 'Assesment and Selection' WHEN 'For Assesment' THEN 'Assesment and Selection' WHEN 'Passed Initial Interview' THEN 'Assesment and Selection' WHEN 'Passed Profiles Assesment' THEN 'Assesment and Selection' WHEN 'Passed Technical Exam' THEN 'Assesment and Selection' WHEN 'For Placement' THEN 'Placement' END) AS phase FROM( SELECT subject, regardingobjectidname, createdon, modifiedon, initial, (CASEinitialWHEN '1 stage' THEN 'Application Recieved' WHEN '2 stage' THEN 'Application Recieved' WHEN '3 stage' THEN 'For Screening' WHEN '4 stage' THEN 'Shortlisted' WHEN '5 stage' THEN 'For Assesment' WHEN '6 stage' THEN 'Passed Initial Interview' WHEN '7 stage' THEN 'Passed Profiles Assesment' WHEN '8 stage' THEN 'Passed Technical Exam' WHEN '9 stage' THEN 'For Placement' WHEN '10 stage' THEN 'For Placement' END) AS stat FROM( SELECT subject, regardingobjectidname, createdon, modifiedon, (CASEsubjectWHEN N'application received' THEN '1 stage' WHEN 'process application' THEN '2 stage' WHEN 'screen application' THEN '3 stage' WHEN 'Phone interview' THEN '4 stage' WHEN N'initial interview' THEN '5 stage' WHEN 'profiles assessment' THEN '6 stage' WHEN 'technical exam and interview' THEN '7 stage' WHEN 'background and reference check' THEN '8 stage' WHEN 'Job Offer' THEN '9 stage' WHEN 'Contract Signing' THEN '10 stage' END) AS initial FROM(SELECT subject, regardingobjectidname, createdon, modifiedon FROMFilteredTask WHERE (subject IN ('application received', 'process application', 'screen application', 'initial interview', 'profiles assessment', 'technical exam and interview', 'background and reference check', 'job offer', 'contract signing')) UNION ALL SELECT subject, regardingobjectidname, createdon, modifiedon FROMFilteredPhoneCall WHEREsubject = 'phone interview' ) AS Orion ) AS Initials ) AS Phases ) AS Stats ORDER BY regardingobjectidname, initial I appreciate your help. Thanks a lot. __________________________________________________ Your future is made by the things you are presently doing. Andrew
View Replies !
View Related
Average Computation Question
My table is laid out as such:ID (int) What (varchar 20) TimeStamp (smalldatetime)------- ------------- ---------------73 Start <T1>73 Misc <T2>73 End <T3>81 Start <T1'>81 Misc <T2'>81 End <T3'>....I need to calculate End - Start for each unique ID (i.e. T3-T1 andT3'-T1') and then take the average of those (2 in this case) entries.Any help is appreciated.Alex.
View Replies !
View Related
Calculate Average Balance
hi experts, i created a report using cube and excel. The report has 2 columns i.e. Date on the left and Term on the right. It has a measure which is Rate. The report requires a row which store the average balance. average balance = total daily rate / no. of days in the month This average balance need to be displayed underneath the Grand Total in excel. How can i display this row in the excel? Another problem i'm facing is if the layout is not so restricted, i have problem calculating the average balance. i'm not sure how the mdx statement should be written. i tried this statement but it gives me the wrong value: avg([Date Tx].[month].Members,[Measures].[Mobile Rate]). i tried using CurrentMember but i have syntax error. please help. Thanks
View Replies !
View Related
MSAS: Trimmed Average
Hi, I have the following table: OrderNumber varchar(15) Completed int(1) Certificate int(1) ThroughputTime int(4) With the following data: OrderNumber Completed Certificate ThroughputTime 00001 1 1 1 00002 1 1 2 00003 1 0 1 00004 1 0 1 00005 0 1 2 00006 0 1 1 00007 0 0 4 00008 0 0 89 I have a dimension: All Completed - Completed - Not Completed and a dimension: All Certificate - No Certificate - Certificate I also have measures: TptSum which Sums ThroughputTime TptCount which Counts OrderNumbers TptAvg which divides TptSum by TptCount My problem is with the next measure: In MSAS I created a Cube which shows the measures on the columns and both Completed and Certificate on the rows. The OrderNumber selection is set to "All Ordernumbers". The measures as listed above are displayed exactly as I expect them to do. TptSum gives the sum for the subselection showed on the left. TptCount and TptAvg also show the expected values on their distinct rows. I can't upload a screenshot but it looks like this: TptSum TptCount TptAvg All Certificate All Completed 101 8 12.63 Completed 5 4 1.25 Not Completed 96 4 24.00 No Certificate All Completed 95 4 23.75 Completed 2 2 1.00 Not Completed 93 2 46.50 Certificate All Completed 6 4 1.50 Completed 3 2 1.50 Not Completed 3 2 1.50 So far so good. Each datarow only takes the records that match the criteria at the left. Now however, To exclude exceptions that have a huge impact on the average I want to create a trimmed average. That is, I want to exclude the top 25% values in my trimmed average measure, which are the 2 records with the highest ThroughputTime, being ordernumber 00007 and 00008 in the "All Certificate" & "All Completed" row, but different in the other rows. I also want the bottom 25% values to be trimmed, which are the 2 records with the lowest ThroughputTime, being ordernumber 00001 and 0003 in the "All Certificate" & "All Completed" row. I tried a lot of things, but i can't get it working. Somehow all my statements don't take the subselections on the rows into account. Really frustrating. What I want to achieve is the following: TptSum TptCount TptAvg TrimmedAvg All Certificate All Completed 101 8 12.63 1.50 ((2+1+2+1) / 4) (middle 4 records) Completed 5 4 1.25 1.00 ((1+1) / 2) (middle 2 records) Not Completed 96 4 24.00 3.00 ((2+4) / 2) (middle 2 records) No Certificate All Completed 95 4 23.75 2.50 ((1+4) / 2) (middle 2 records) Completed 2 2 1.00 1.00 ((1+1) / 2) (all 2 records) Not Completed 93 2 46.50 46.50 ((4+89) / 2) (all 2 records) Certificate All Completed 6 4 1.50 1.50 ((1+2) / 2) (middle 2 records) Completed 3 2 1.50 1.50 ((1+2) / 2) (all 2 records) Not Completed 3 2 1.50 1.50 ((1+2) / 2) (all 2 records) I hope I describe my problem well... I also hope someone knows a solution. I can also post the things I tried, but I'm afraid that makes it more confusing, because nothing worked ;) Thanks, Edward PS. newbie alert! :beer:
View Replies !
View Related
3 Month To Date Average
Hello Friends Actually i posted this problem earlier , hoping that i got the result , did not look in depth untill i realized the values i got was getting was wrong. The Scenario is still the same. 3 MTD should be the past two completed months as well as the dates in the current month as well. So taking the example above if the reporting period for Feb was 01/30/2007 to 02/27/2007, and March was 02/28/2007 to 03/27/2007, the 3 MTD should then be the average from 01/30/2007 to 04/10/2007. I am creating a Calculated Member as measure. Basically if i select the average on 76th day of the year, it should basically be the average of 76th Day ( well offcourse whould ignore the empty cells ) For Example the result set should be very similar , It is basically doing MTD Running balance is basically adding up , where as 3 month to date average should be average of 3 month previous average suppose today is 75th day , so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month) I have used the lag function AVG(Hirearchy.Currentmember.lag(2):Hirearchy.Curre ntmember, Measure.abc) where hirerachy is Year->Month-date The problem i have here is which i am coming close to conclusion When i use month level it gives me average of month level , the result on month level is fine. But my requirment is to have it on date level. but how do i have rolling average of 3 month in a date level, if i do a date level with 90 days lag which is not correct which is average of 90 days from current day. AVG([Tbl Date Key].[Report Hirerachy].CurrentMember.Lag(2):[Tbl Date Key].[Report Hirerachy].CurrentMember,[Measures].[Ab1_Avg] ) When i drill down to date level , which would be assumed AVG([Tbl Date Key].[Report Hirerachy].date.Lag(2):[Tbl Date Key].[Report Hirerachy].date,[Measures].[Ab1_Avg] ) its doing a lag on 3 days lag as appose to , I need the lag on 3 months on a day which would be 90 days should i be doing a lag on days. The problem at 90 days lag would be every it would lag 90 days average , but what i am looking for it is when it is on the middle of the month it should be suppose today is 15th day of month, so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month) i am confused , please help
View Replies !
View Related
Average Size Of A Row In A Table
How do I find the average size of a row in a table? I need to calculate a row size in a number of tables, then sum those to find the average size of one record ( a hotel guest in this case), which includes entries in a dozen tables. Thanks
View Replies !
View Related
MSAS Trimmed Average
Hi, I have the following table: OrderNumber varchar(15) Completed int(1) Certificate int(1) ThroughputTime int(4) With the following data: OrderNumber Completed Certificate ThroughputTime 00001 1 1 1 00002 1 1 2 00003 1 0 1 00004 1 0 1 00005 0 1 2 00006 0 1 1 00007 0 0 4 00008 0 0 89 I have a dimension: All Completed - Completed - Not Completed and a dimension: All Certificate - No Certificate - Certificate I also have a measures: TptSum which Sums ThroughputTime TptCount which Counts OrderNumbers TptAvg which divided TptSum by TptCount My problem is with the next measure: In MSAS I created a Cube which shows the measures on the columns and both Completed and Certificate on the rows. The OrderNumber selection is set to "All Ordernumbers". The measures a listed above are displayed exactly as i expect them to do. TptSum gives the sum for the subselection showed on the left. TptCount and TptAvg also show the expected values on their distinct rows. I can't upload a screenshot but it looks like this: TptSum TptCount TptAvg All Certificate All Completed 101 8 12.63 Completed 5 4 1.25 Not Completed 96 4 24.00 No Certificate All Completed 95 4 23.75 Completed 2 2 1.00 Not Completed 93 2 46.50 Certificate All Completed 6 4 1.50 Completed 3 2 1.50 Not Completed 3 2 1.50 So far so good. Each datarow only takes the records that match the criteria at the left. Now however, To exclude exceptions that have a huge impact on the average I want to create a trimmed average. That is, I want to exclude the top 25% values in my trimmed average measure, which are the 2 records with the highest ThroughputTime, being ordernumber 00007 and 00008 in the "All Certificate" & "All Completed" row, but different in the other rows. I also want the bottom 25% values to be trimmed, which are the 2 records with the lowest ThroughputTime, being ordernumber 00001 and 0003 in the "All Certificate" & "All Completed" row. I tried a lot of things, but i can't get it working. Somehow all my statements don't take the subselections on the rows into account. Really frustrating. What I want to achieve is the following: TptSum TptCount TptAvg TrimmedAvg All Certificate All Completed 101 8 12.63 1.50 ((2+1+2+1) / 4) (middle 4 records) Completed 5 4 1.25 1.00 ((1+1) / 2) (middle 2 records) Not Completed 96 4 24.00 3.00 ((2+4) / 2) (middle 2 records) No Certificate All Completed 95 4 23.75 2.50 ((1+4) / 2) (middle 2 records) Completed 2 2 1.00 1.00 ((1+1) / 2) (all 2 records) Not Completed 93 2 46.50 46.50 ((4+89) / 2) (all 2 records) Certificate All Completed 6 4 1.50 1.50 ((1+2) / 2) (middle 2 records) Completed 3 2 1.50 1.50 ((1+2) / 2) (all 2 records) Not Completed 3 2 1.50 1.50 ((1+2) / 2) (all 2 records) I hope I describe my problem well... I also hope someone knows a solution. I can also post the things I tried, but I'm afraid that makes it more confusing, because nothing worked ;) Thanks, Edward
View Replies !
View Related
Average Of Previous Values
How to write Stored Procedure to convert All seconds to Minutes AND finding average. Ex: My Table1: SYMBOL TIME PRICE EUR A0-FX 2008-05-09 11:37:31.203 1.54035 EUR A0-FX 2008-05-09 11:37:30.030 1.54034 EUR A0-FX 2008-05-09 11:37:28.860 1.54033 EUR A0-FX 2008-05-09 11:37:41.673 1.54032 EUR A0-FX 2008-05-09 11:37:59.720 1.54031 EUR A0-FX 2008-05-09 11:38:09.000 1.54033 EUR A0-FX 2008-05-09 11:38:35.877 1.54032 EUR A0-FX 2008-05-09 11:38:59.767 1.54041 OutPut: SYMBOL TIME PRICE EUR A0-FX 11:37 1.54031 EUR A0-FX 11:38 1.54041 I know this how to write .. ;WITH cte AS ( SELECT SYMBOL, [Time], Price, ROW_NUMBER() OVER(PARTITION BY CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) ORDER BY CAST(Time AS DATETIME) ASC) AS rn_1, ROW_NUMBER() OVER(PARTITION BY CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) ORDER BY CAST(Time AS DATETIME) DESC) AS rn_2 FROM Table1 WHERE SYMBOL='EUR A0-FX' ) SELECT SYMBOL='EUR A0-FX',CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) AS [Time],MAX(CASE WHEN rn_2 = 1 THEN Price ELSE NULL END) AS [Close] FROM cte GROUP BY CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) ORDER BY CAST(CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) AS DATETIME); But I want to add some additional code in my procedure like AVERAGE of Previous 5 Price Values. Ex: SYMBOL TIME PRICE EUR A0-FX 11:37 1.54031 Ã1 EUR A0-FX 11:38 1.54041 Ã2 EUR A0-FX 11:39 1.54021 Ã3 EUR A0-FX 11:40 1.54081 Ã4 EUR A0-FX 11:41 1.54071 Ã5 Previous 5 Average PRICE Values. (1.54061) EUR A0-FX 11:42 1.54091 Ã6 EUR A0-FX 11:43 1.54021 Ã7 EUR A0-FX 11:44 1.54081 Ã8 My Final Out Put: SYMBOL TIME PRICE AVERAGE EUR A0-FX 11:42 1.54091 Ã6 (1.54061) EUR A0-FX 11:43 1.54021 Ã7 (1.54091) EUR A0-FX 11:44 1.54081 Ã8 (1.54071) At 11.42 time average is 1-5 price values At 11.43 time average is 2-6 price values At 11.44 time average is 3-7 price values
View Replies !
View Related
Average Of Privious 10 Values..
Hi i am traying to convert privious 10 values. How to write a query to convert privious 10 Values. My table Columns like this... SNO PRICE AVG I have 10 values in my table, in the 11 th row i want to diaplay avg of 10. i.e average of 1-10 values. Simlarly sno(2-11) of average and sno(3-12) of average.. .e.t.c Any one please help me on this problem
View Replies !
View Related
Query For 4 Weeks Average ..Need Help
i have 3 tables, each with a date(it has daily dates) column(column name is same in all tables) Each table has columns say "value1","value2", "value3" i want data from all these tables together.such that my first column will have data weeks and other 3 columns count1,count2,count3 will have average of next 4 weeks count..placed infront of week. weeks count(value1) count(value2) count(value3 ) 1/1/2005 101 88 221 1/8/2005 100 81 151 1/15/2005 87 96 301 Average calculations Here : week 1 2 3 4 Count1: 101 = ( 99 + 105 + 110 + 87 )/4 100 = (105 + 110 + 87 + 98 )/4 Plz lemme know if u have any suggestions..
View Replies !
View Related
Average Functionality Issue
Hi All, In my Report using SSRS 2005, I am displaying data using table and this table has table footer in which I am calculating "average" using Avg() function.The values in table rows can be either floating numbers (for eg; -1,-99,5.005) or "N/A". I use the following function to calculate Average =Avg(Iif(Fields!Channel1.Value="N/A",CDbl("0"),CDbl(Fields!Channel1.Value)),"MainDS") where "Fields!Channel1.Value" are values in table rows. Everything works fine if all the table rows are floating point numbers and not "N/A", but as soon as table rows values are "N/A" I encounter "#Error" in the table footer. Can anyone figure out how to get this code working. Thanks in advance. Regards, abhi_viking
View Replies !
View Related
Average Not Using Calculated Member
Hi I need to calculate an average over a dimesion other than time and "average of children" won't do that for me. I'm aware I can create a calculated member where I divide a sum by a count but the problem I have is I need use this calculation in a performance point scorecard and I want to drill down on the measure and you can't do this on calculated members. Is there any other way of obtaining an this type of average? Thanks in advance.
View Replies !
View Related
Subtotal Average Matrix
hello, my problem is in a report with matrix, i use aggregate funtion, but all the cells appears Empty , if i not use aggregate the cells appears well , but the subtotals is bad. = aggregate( field!myaverage.value) (myavergae is measure mdx) YearCOLUMGROUP average measure MonthCOLUMGROUP 2007 TOTAL PGROWGROUP PSGROWGROUP JANUARY FEBREUARY P1 SP1 12 2 P2 SP2 2 4 P3 SP3 1 5 P4 SP4 2 4 TOTAL how can i use aggregate by the subtotales and without the aggregate by the rest?
View Replies !
View Related
Average Transactions By Weekday
Hi, I have a transaction file containing a year's worth of transactions with a date/time field that tells me when the transaction took place. I'd like to write a view (or perhaps multiple views) that tells me the average number of transactions per weekday. I've been messing around with DATEPART and I can calculate the *total* number of transactions that took place on a Monday, for example, but I can't figure how to do the *average* number. My problem seems to be calculating the number of Mondays there were in the year. Or overthinking it. Any suggestions would be greatly appreciated. Thanks, Jennifer
View Replies !
View Related
|
TRACKER
