Filtering finances by year

I’m using the fibery finance table. I want to create a view for the transactions by category
The easiest way would be to filter the transactions by their date, but because the lookup of the categories happens on the back end and not related to the filtered view, this data isn’t related to the view.

Thought of adding this as a feature request, but it changes a lot of fundimentals about the lookup. So I’m wondering if there’s another work around?

Thanks!

Not sure I understand everything you’re saying.

what is this?

Can you explain a bit more what you mean by this.

Maybe some screenshots would help…

1 Like

Sorry I wrote this on the bus. I meant the fibery finance template.

See screenshot.

The category sum is calculated on the backend. No matter how I filter the front-end.

I want to make a view just for 2024. Then a new one for 2025.

Or is there any other way to get the annual accounts? Report maybe? I want to be able to get a report year by year.

The view you shared is for the (current) balance for each account by category.

What would it mean to calculate this for a specific year?
Do you just want to see the total of incoming and outgoing transactions for a given year?
Or do you want to see the balance on 31st December of each year?

This one. My solution would be to filter the transactions by date. But this doesn’t effect the sum of the category.

Edit: nvm there’s no transactions in the view I showed you’re right. But the look up totals are from related transactions that are dated.

I suggest making a report view (table), based on Transactions, with a column that is this:

IF(
	SPLIT(
		[Encoding],
		'|'
	) == '>',
	[From account],
	[To account]
)

and another column which is this:

SUM(
	IF(
		SPLIT(
			[Encoding],
			'|'
		) == '>',
		-[Amount],
		[Amount]
	)
)

Then you can group by date, aggregated by year (make sure you check the ‘Show subtotal’ box):

It should get you something like this:

Each account shows the subtotal of all incoming/outgoing transactions for each year

3 Likes

Hey! I’m just getting to making this now. This works for some reason but I’m so confused as to why… (Nevermind just found this: https://www.youtube.com/watch?v=sK2RTuQZdmI)

I want to show the parent account and category for each one as well. Is there a way to do that as well?

I’m getting it slowly. Is it possible to do recursive parent in report view?

You can make a recursive formula field which gets the very top level parent, but there is no syntax for doing that in report view.

Okay makes sense. Lets stick to top level for now them. I tried to group like this:

IF(
SPLIT(
[Encoding],
‘|’
) == ‘>’,
[From top level category],
[To top level category]
)

But for some reason some entities are in no group at all. In the source data it shows its empty. But when I go into the entity it does in actuality have a parent to account (and all the related formulas). I suspect something weird is happening when spliting the data? Some data is lost maybe? I am not sure. I sat on this yesterday all day haha.

After 2 days of tinkering, turns out that when I make the report on “historical data”, not all the data comes through… Not at all sure why, if this intended or a bug? I set it to Current data, used the group formula above, and now it works like a charm.

If, after reading the guide on historical reports and the guide to context views, you think some data is incorrectly missing from your report, let us know.

Thanks Chris. Still all the reporting capabilities. I’m looking into it more now. The thing is, the historical view doesn’t have a filter on, so I expect (incorrectly?) that is it all data and should be the same as current data. I see that actually the data for the account is there, but the formulas that find the category of the account is missing (on some of the data, not all of it). Not sure exactly… In any case, now I understand the use case for historical data. It could be a good way to go about reporting if the transactions were live integrated into the account, but since we only update it once in a while, it would not be accurate info from what I understand. Thanks Chris!

The data in the historical report will not be ‘the same’ as the current data but rather will be a collection of data points over the lifetime of the entities (which means it can be considered a superset of the current data).
If you add a formula field, the calculated value of that field will not exist for all data points (events) in the historical report.

1 Like