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.
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
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?)
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).