Number of months

Hi,

Some of my requests need the number of months of the current year, e.g, to know the average money by month for the current year. Currently I input myself an integer but it would be more efficient if I could use a variable.

In the above request, in “view” I use the integer 2 because we’re in February.

In “view” I’d like to have something like “database variable” and “number of months in current year”.

Maybe I just couldn’t find how to get this variable while writing the question.

Hi, I hope I understand your question right - you want to see the sum of your payments by month?
Then you should group by (at the right side of your query builder) the relevant date field by month:

Then you see the sum of payment amounts per month in a table.

Does that answer your question?
Cheers, Eva

1 Like

Hi Eva,

It was obvious and I already use it in other questions. Don’t know why I couldn’t get it. Many thanks to you!

1 Like

Second thought, I am not looking for the sum of payment amounts per month, more exactly the average of payment amounts in a month. My request should return only one number.

Ah … I am not sure if you can do that with the query builder (depends a bit how your table is structured). But when you use a SQL database you could write the syntax in SQL? Have you tried that already? Happy to help :slight_smile:
Cheers E

Have you tried using the “Average of” in the “view” area? As said, it depends on how your table is structured. I guess for everything a bit more complicated you would have to switch to SQL syntax.

Yeah, in fact in SQL i would write a query with a sub-query : first would get the average of payment amounts by month (so in 2018 2 values, January and February) and after that the average of these 2 values.

Hi, as I don´t exactly know what you are trying to achive the question might be dull but why do you need to average the payments per month at all? why can´t you just average all payment sums from the beginning of the year? (and that could be maybe achieved with the average of in the view section?)

Lets take an example : I got a total amount of 100€ in January and a total amount of 50€ in February, I would like to get the average of these two numbers, so finally 75€.

Ah, get it, you don´t want to have the average of the payments but the average of the sum of payments.
Yes I guess for that you would have to use SQL …

I succeeded using directly the SQL. Thanks for your support!

1 Like

Just an idea … trying to push the limits https://www.youtube.com/watch?v=S29GrVANbHM a bit here :wink: on “can it be done using the GUI, or do you have to drop one level closer to the metal, and resort to SQL queries”. Maybe you can exploit that the Metabase team in the v0.25 introduced nested questions - they later blogged about it as we put questions inside your questions

Disclaimer: The above hasn’t been fully thought through or tried out … just leaving it here as a teaser for you/anyone to a prove :+1: or disprove :-1:

1 Like

Yeah, as Jorn is pointing out, another way to do this without SQL is to first create a saved question that is the sum of payments per month. Then, start a new question that uses that previous saved question as its data source, then do the average of the Payments column.

The high level thing you’re trying to do is an aggregate of an aggregate (the average of a sum), which is a tricky problem that we are interested in tackling in the foreseeable future.

2 Likes

Great sum up of the core problem for this kind of common queries!

Excited to hear the core developers have this high on the roadmap. Do you guys already have a firm idea about implementation and is there anywhere where this is getting spec’ed out already? Or would it be helpful to have it somewhere for the community to chime in on?

Looking around I see a lot of GitHub issues and discussions here touching on aspects of this subject. Would collecting (cough aggregating, sorry) pointers to these more systematic be worthwhile? (I think with a broader/more generic way to look at the problem space the easier it is to “get right” rather than solving issues one by one).

Thoughts?