x
Solved

# Calculating monthly averages

Posted on 2015-02-10
Medium Priority
104 Views
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;
``````
0
Question by:chtullu135
• 2

LVL 50

Expert Comment

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 53

Accepted Solution

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

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 53

Expert Comment

ID: 40618260
You are welcome!

/gustav
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.