[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Calculating monthly averages

Posted on 2015-02-10
4
Medium Priority
?
101 Views
Last Modified: 2015-02-18
Hello,

I've written the following code that displays the daily difference between two dates of the NE_Percent_Avail_Daily field via this portion of the code.  I'm now trying to figure out how to display the average of all the values of the NE_Percent_Daily field for each month and display that value in the first entry for that month in a calculated field.  The number of entries in a month can vary.

[NE_Percent_Avail_Daily]-(select NE_Percent_Avail_Daily from SummaryTable where NE_Entry_Date =
(select max(NE_Entry_Date) from SummaryTable where NE_Entry_Date < S.NE_Entry_Date)) AS NEDailyChange,

SELECT S.NE_Entry_Date, S.NE_Items, S.NE_Items_Avail, S.NE_Percent_Avail_Daily, [NE_Percent_Avail_Daily]-(select NE_Percent_Avail_Daily from SummaryTable where NE_Entry_Date =
(select max(NE_Entry_Date) from SummaryTable where NE_Entry_Date < S.NE_Entry_Date)) AS NEDailyChange, S.NE_Percent_Avail_MTD, S.NE_Monthly_Change, S.NE_Percent_Avail_Yearly, S.NE_YTD_Change, S.ColumnSpace1, S.LTN_Entry_Date, S.LTN_NE_ITEMS, S.LTN_Avail, S.LTN_Percent_Avail_Daily, [LTN_Percent_Avail_Daily]-(select LTN_Percent_Avail_Daily from SummaryTable where LTN_Entry_Date =
(select max(LTN_Entry_Date) from SummaryTable where LTN_Entry_Date < S.LTN_Entry_Date)) AS LTNDaily_Change, S.LTN_Percent_Avail_MTD, S.LTN_Monthly_Change, S.LTN_Percent_Avail_YTD, S.LTN_YTD_Change, S.ColumnSpace2, S.QRO_Entry_Date, S.QRO_Items, S.QRO_Avail, S.QRO_Percent_Avail_Daily, [QRO_Percent_Avail_Daily]-(select QRO_Percent_Avail_Daily from SummaryTable where QRO_Entry_Date =
(select max(QRO_Entry_Date) from SummaryTable where QRO_Entry_Date < S.QRO_Entry_Date)) AS QRODaily_Change, S.QRO_Percent_Avail_MTD, S.QRO_Monthly_Change, S.QRO_Percent_Avail_YTD, S.QRO_YTD
FROM SummaryTable AS S
ORDER BY S.NE_Entry_Date;

Open in new window

0
Comment
Question by:chtullu135
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40602109
Are you saying that you want a running average, which would give you the average of the previous X (maybe 30) days for every date in a sequence?  Or that you simply want the 12 monthly averages?

Can you provide an example of what the output would look like?
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40603765
You can use an expression like:

MonthAverage: IIf(Day(S.NE_Entry_Date)>1, Null, DAvg("YourAmountField","YourQuery","YourDateCriteria"))

/gustav
0
 

Author Closing Comment

by:chtullu135
ID: 40618110
Thanks Gustav.  I 'm sorry for the delay in replying but for some reasons, I can't post my replies from my computer at work and my closing of questions did not go through.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40618260
You are welcome!

/gustav
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question