Hello every one !
I have been stuck for hours on this issue.
My app is a simple expenses & budget tracker.
I have 3 mains tabs : monthly report, expenses, categories.
When I add an expense, this one is tied to a date (month & year) and a category.
In my category tab, i can select a category and then see all the expenses in it grouped by month. I want to show a graph where I see the average expense of this category per month. BUT not per unique month (June 2021 & June 2022) but global month : all the months of June.
This is what I canât manage to do.
In the first picture you see a graph where I see the sum of the expenses in this category per month, but in 10 yeart I will have 120 months which is not convenient. So i tried to group by month (picture 2), but it makes the addition of them, and i canât seem to find a way to divide it.
You can take as an example january where I want an average of 259⏠(518/2)
Thanks in advance !
Lucas
I think what you can do here is:
-
Add a helper table with 12 rows to store 12 months (from January to December).
-
I assume you already derive the month name from each expense in the Expenses table. (There are a couple ways to do this, one is deriving the month index first using a math column: MONTH(Date) - 1, then use a single column to point to the column of names in the first step, choosing âfrom startâ)
-
Another assumption: Youâre writing the category name to the Expenses table (it would be better if you write a category ID since the name can change). On entry to each category details view, have a set column action to write the category name/category ID back to your user profiles row.
-
Go to the helper table in the 1st step, create a template using the Month Name - Category Name/ID. E.g: January - Soirées & Sorties.
-
Go to the Expenses table, create the same template.
-
Make a multiple relation from the helper table to the expenses table using the 2 columns above.
-
Have a rollup column to get the average of each month in the helper table.
Then point your chart to that helper table, and display the rollup average column.
Maybe someone will have a better way than me, but at first thought I think this would work.
2 Likes
Thanks for your answer !
I think that this indeed would work !
I already have a helper table for the months, and created a template in my expense table (January - SoirĂ©es & sorties). But I donât understand this step :
Another assumption: Youâre writing the category name to the Expenses table (it would be better if you write a category ID since the name can change). On entry to each category details view, have a set column action to write the category name/category ID back to your user profiles row.
How do I add the category in the user profile row ? I think thatâs the missing part 
You create manually one template column by category (+month) in the helper table is that it ?
EDIT : Tried it, and it works thanks a lot!
1 Like
You use a set column action on entry to the categoryâs details screen. If itâs configured correctly, you can point it to the user profileâs row and use that value later on in the template configuration.
The solution i tried with your help works :
You create manually one template column by category (+month) in the helper table is that it ?
But its kind of hand made, I have to create these template columns by category which is not convenient if i change, modify or add new one. I think i get the logic of your idea :
You use a set column action on entry to the categoryâs details screen. If itâs configured correctly, you can point it to the user profileâs row and use that value later on in the template configuration.
But I donât really understand how you do it ?
Why do you have to create it by category though? If you point the template column to the category name then you should not have to manually change anything.
But I didnât manage to do it which is why my template column in the helper table is âmanualâ : MONTH SoirĂ©es & sorties, where MONTH depends on the row and the italic text, I wrote it.
I think youâre missing this part.
Exactly, thatâs what I donât understand.
