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.
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:
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
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 …
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.
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 heretouching 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).