Base Reports - Getting Monthly & YTD Averages

users
#1

Hi Don,

shown, and that works great. I used the "Accumulation" function to do
this. But when I try to get an overall average, I wind up getting the
average for the first month (in the report), not for the year-to-date.

What expression are you using in the definition of your Report Builder
field to obtain the average of all months combined ? If you just use
something like AVG[MONTH] then it will only average on that particular
month.

It might be better, as Tom has suggested, to create a separate result in
your initial query in order to provide the average of the combined
months rainfall.

Alex

#2

Thanks Alex. The underlying query gets the month from the date, as well as
the total, average, max and min for each month. The results are, of
course, already grouped & calculated by month. I was able to get the
overall total, but also wanted to see the overall average, max and min. I
somehow imagined that it would be a piece of cake. It looks like Calc is
the better "report tool" in this case.

In Calc, I dragged the query into a spreadsheet, and simply created a
series of formulas to perform the calculations on the results.

If I alter the query, how can I get the overall average, apart from the
monthly averages?

Thanks,
Don

#3

Hi :slight_smile:
I was thinking about a 2nd Query to get the average/month rather than using
the 1st Query. Alex has a better idea i think.

As for using Calc i thought the idea was to use something like;
Insert - Data Source
but i could only find linking to "External data". I didn't think that just
dragging the Query into Calc would work. I'm guessing that if the original
data now gets updated the ex-Query in Calc wouldn't update? At least it's
given a basic idea of what to expect though.

I don't know how to get Calc to generate reports based on Queries within
Base but i really should.
Apols and regards from
Tom :slight_smile:

#4

Having never really played around with Calc and database datasets, I
admit to not knowing, but I imagine that the function set is broader and
thus allows for more possibilities.

Would you mind posting your initial query and table definition so that I
could build my own little test db and have a think about it, otherwise
its all a bit too abstract ?

Alex

#5

Sure.

The table:
  id date amount_mm 0 08/12/14 22 1 08/19/14 8 2 08/24/14 9.5 3 09/04/14
52 4 09/11/14 20 6 09/12/14 20 7 09/13/14 34 8 09/17/14 42 9 09/24/14 7
The underlying query:
SELECT MONTHNAME( "date" ) AS "Month", SUM( "amount_mm" ) AS "Total (mm)",
AVG( "amount_mm" ) AS "Avg (mm)", MAX( "amount_mm" ) AS "Max (mm)", MIN(
"amount_mm" ) AS "Min (mm)", SUM( "amount_mm" * 0.0393701 ) AS "Sum (in)",
AVG( "amount_mm" * 0.0393701 ) AS "Avg (in)", MAX( "amount_mm" * 0.0393701
) AS "Max (in)", MIN( "amount_mm" * 0.0393701 ) AS "Min (in)" FROM
"tblRain" GROUP BY MONTHNAME( "date" )