TLDR: In the Question builder UI, is it possible to group data on the x axis by both month and year of creation.
I am trying to create a question to capture the number of items created, the number of items completed, and the completion rate, grouped by month of the year created and in order from the beginning of my time window up to the most recently completed month.
All is working as expected, except for the x-axis. I want to show a running month-year axis, with the most recently completed month on the right hand side and the most recently completed month minus, say, 12 or 24 months on the left hand side. Currently, I can only get the sum for each category for each month, regardless of the year. Is there a way to solve for this, while still using the question builder UI?
Hey there, do you mind sharing an example, maybe from another application, or drawn by hand?
Sure -- as you can see in this sample data, the x axis goes from january to december, but as it is July and I want the x axis to reflect a rolling 12 month interval through the most recently completed month, it should go from july 2023 through june 2024.
As you can see, the month of July in the screenshot has values much greater than the other months, as it is currently double counting July when I use the question builder's internal 12 month time interval (as it's currently capturing july 17th 2023 through july17th 2024).
I know I can set the question builder to start from the last completed month and then go back 12 months, but that isn't really the issue here. I want a rolling 12 month interval, with the most recently completed month always appearing on the right side of the x-axis.
I also know I can build a custom SQL query, but due to how my data is structured, that makes setting field filters very tricky in the dashboard I want to include this figure in. I converted all my other custom sql queries to question builder UI figures and this is the last stubborn one.
using the same sample data, this is what I want the question builder UI to generate:
I think to solve your issue you can use something like:
SELECT TO_CHAR(some_date, 'yyyy-mm-01')::date
In this way you always take the first day of the month, including also the year, so any trend line time-based will work fine.
The sql above might be different in case you use specific databases, the example shown is about postgresql.
Hope this helps!
