AnsweredAssumed Answered

Filter, Sort by Year, Average of Monthly Totals, Chart

Question asked by jtorpy on Mar 3, 2018
Latest reply on Mar 7, 2018 by jtorpy

I have multiple location fields (Country, City, Block, etc), Year, Month, and Quantity. I'm filtering results in some combination of location fields. Then sorting by Year so it will display Yearly info on the chart. What I need though is the average of the monthly totals (not the simple average of all entries in the year).


The options I see when putting the fields and reports together aren't sufficient. Nothing seems to give me a way to start with a layout, filter by location, sort by year, and get Yearly Average of the (Total Quantity of each Month).


Below is a clarified example as well as a few links I followed. If you have a $variable or ExecuteSQL suggestion, I'm open to that, but please clarify where it goes (chart, Field calculation, global field calculation, etc). I'm planning not to do a virtual list for this.


Thanks for any help you can provide.





Clarified example:

I want to start with the Found location, sorted by Year for charting, so it might have any number of countries or cities. I want to calculate for the found set an average of all entries for the month. So if the search returns below 4 entries in july and 5 in august, and those are the only two for 2017, then my average for the month would be, in the example below,

(((15+14+10+8)/4) + ((11+12+45+11+9)/5)) / 2 = 14.675  (not sum/9=15)


(excludes location info)


jbrown suggested an ExecuteSQL but it wasn't clear if this went in a calculated field or a script, or what function the script would wrap in for the Get to work.

Month's Average: ExecuteSQL("SELECT AVG(Reading) FROM YourTable WHERE ReadingMonth = ?" ; ""; "" ; Month (get(CurrentDate)) )

calculating average for this month


erolst had a good rundown of reporting and summary fields. It started with 'create summary field'. Not sure if that's create average or total or count or what.

Yearly Averages