Is it possible to use data from an account which is not the entire period for that fiscal year?
I want to create a template to calculate the VAT-deductability. This needs always to be calculate on 31/12 with no regard on how the fiscal year is.
So for fiscal year that end on 31/12 I have no problem.
But how can you solve that when the fiscal year ends for instance 30/09?
I need then the data 01/10 - 31/12 of the current year and 01/01 - 30/09 of the previous year.
You probably mean that, when the current period is 30/9 (bookyear 1/10 - 30/9), you want to have the year-to-date value of a certain account? I’m a bit confused, sorry
Anyway, if you call on #61 in a period, it will give you the year-to-date value from the start of the book year until your current period.
Want to call upon previous period? That’s also possible, with adding :1p to go back one period, or :1y to go one bookyear back.
For example:
{{ #61:2p | currency }} gives the YTD value of 2 periods back
{{ #55:1y | currency }} gives the YTD value of current period for previous bookyear
just to be sure… If you have a bookyear running from 1/10/2019 - 30/9/2020, you then want the value of accounts split in:
1/1/2019 - 31/12/2019 (so previous calendar year)
1/1/2020 - 30/9/2020 (so until current period)
Is that correct? Because in above scenario we are looking into period-to-date values from previous book year, together with period-to-date values from current book year.
Also, we are going to need logic related to the company report frequency (quarterly or monthly), to see how many periods we do need to go back
So I just want to be sure we understand the goal you are trying to accomplish here (as it’ll be a bit harder to do).
In this case, I woud need the value of the previous calendar year 01/01/2019 - 31/12/2019 and 01/01/2018 - 31/12/2018 to be able to check the difference between both years.
I think it’s clear for us now what you are trying to build, however due to the high complexity we will need some time to investigate this and provide you with a proper solution.
We will get back to you on this topic in the course of this week.
We wanted to update you that we didn’t have the time yet to look more closely into your issue. As mentioned, due to the high complexity it will take a time effort from our end to investigate.
We have taken this up into our planning for the upcoming weeks.
{% assign month_end_year = period.year_end_date | date:"%m" %}
{% assign current_account_number = current_account.number %}
{% capture current_account_number %}{{ current_account.number }}{% endcapture %}
{% if month_end_year == 12 %}
{% assign account_value = current_account.value %}
{% else %}
{% assign previous_year = period.year_end_date | retract_years:1 | date:'%Y' %}
{% comment %}Looping over PREVIOUS fiscal year values and adding amounts that belong to the natural calendar year{% endcomment %}
{% for transaction in period.minus_1y.accounts.[current_account_number].first.transactions %}
{% assign transaction_year = transaction.date | date:'%Y' %}
{% if transaction_year == previous_year %}
{% assign account_value = account_value+transaction.value %}
{% endif %}
{% endfor %}
{% comment %}Looping over CURRENT fiscal year values and adding amounts that belong to the natural calendar year{% endcomment %}
{% for transaction in current_account.transactions %}
{% assign transaction_year = transaction.date | date:'%Y' %}
{% if transaction_year == previous_year or transaction_year == blank %}
{% assign account_value = account_value+transaction.value %}
{% endif %}
{% endfor %}
{% endif %}
{% comment %}Deducting opening value for BS accounts{% endcomment %}
{% assign account_value = account_value-current_account.opening_value %}
Account value (Jan-Dec) = {{ account_value | currency }}
What I did in the above code was first checking whether or not the end of the fiscal year is in December, if so, I just take the current account value from December (since Silverfin shows YTD values this would be from Jan till Dec).
If the fiscal year ends in a different month, I check which year I want to get the transactions from (e.g. if the end of the fiscal year is 09/20, then I want to get the transactions of Jan-Dec 2019) and that’s why I retract one year. Then I loop over the current and previous year transactions in the ledger and just accumulate the value of the transaction in the variable account_value if the transaction date is within the natural calendar year I want.
Since you only want values from Jan-Dec, I deducted the opening value.
The code provided by Borja was intended to get the value from a particular account (current_account). And I think specially to be used in an account template as well.
But, to make the same check though multiple accounts, you can make some small adjustments:
{% assign accounts = period.accounts | range:5 %}
{% assign total = 0 %}
{% for current_account in accounts %}
... original code here ...
{% assign total = total+account_value %}
{% endfor %}
total {{ total }}
Basically, wrap it in a loop over the accounts you need, and keep track of the total.
One thing that I would like to mention is, since the original snippet is looking at the account’s transactions. Do this multiple times, over a big range of account, could slow down your template. So something to consider when implementing something like this.
