We might want to check which accounts have a wrong end value. For instance in Belgium a 55-account can’t have a negative end-value (credit). If it does, it needs to be corrected to another range (43-range).
This check can be implemented automatically in Silverfin through a template, like this:
Below you’ll find the source code with the addition explanation between comment-tags:
{% comment %}create fixed range for debet accounts{% endcomment %}
{% assign debet_range = "40,6" %}
{% comment %}in the local variable "d_accounts" all debet accounts are taken{% endcomment %}
{% assign d_accounts = period.accounts | range:debet_range %}
{% comment %}we do the same for credit accounts{% endcomment %}
{% assign credit_range = "10,11,12,13,7" %}
{% assign c_accounts = period.accounts | range:credit_range %}
{% comment %}create empty string for d and c accounts - later on, this will be filled with ranges{% endcomment %}
{% assign d_range = "" %}
{% assign c_range = "" %}
{% comment %}loop over all D accounts and take the accounts out of it that have a value less than 0{% endcomment %}
{% for acc in d_accounts %}
{% if acc.value < 0 %}
{% comment %}each account that is less than 0, will be added to the string d_range, separated by a "$,"{% endcomment %}
{% assign d_range = d_range | append:acc.number | append:"$," %}
{% endif %}
{% endfor %}
{% comment %}all period accounts will be filtered on the accounts string we just created - the string that has only those accounts with a value less than 0{% endcomment %}
{% assign d_accounts = period.accounts | range:d_range %}
{% comment %}we do the same for the C accounts{% endcomment %}
{% for acc in c_accounts %}
{% if acc.value > 0 %}
{% assign c_range = c_range | append:acc.number | append:"$," %}
{% endif %}
{% endfor %}
{% assign c_accounts = period.accounts | range:c_range %}
{% comment %}Info box to let users know what ranges are taken{% endcomment %}
{::infotext}
The range to check debet-accounts is: {{ debet_range }}
The range to check credit-accounts is: {{ credit_range }}
{:/infotext}
* * *
{% comment %}create local variable and assign it to 0 - we will calculate with it later on{% endcomment %}
{% assign wrong_accounts = 0 %}
{% comment %}we capture this whole block of code because we need the value of how many accounts are wrong - we display that info first and then the whole block of coding (local variables are read from the top to the bottom){% endcomment %}
{% capture overview_wrong_accounts %}
{% stripnewlines %}
{% for acc in d_accounts %}{% comment %}looping over all wrong d-accounts{% endcomment %}
{% if forloop.first %}{% comment %}is there is at least one account, title will be shown{% endcomment %}
| Accounts that should have a debet value but have credit value instead
{% newline %}
|:----100%----:#
{% newline %}
{% newline %}
| Account link
| Account value
{% newline %}
|----80%----
|----20%----:#
{% endif %}
{% newline %}
| {{ acc.link }}
| {{ acc.value | currency_dc }}
{% comment %}count how many wrong accounts there are - after the capture we will display that and then we display the capture{% endcomment %}
{% assign wrong_accounts = wrong_accounts | plus:1 %}
{% endfor %}
{% endstripnewlines %}
{% comment %}we do the same for the wrong C accounts{% endcomment %}
{% stripnewlines %}
{% for acc in c_accounts %}
{% if forloop.first %}
| Accounts that should have a credit value but have debet value instead
{% newline %}
|:----100%----:#
{% newline %}
{% newline %}
| Account link
| Account value
{% newline %}
|----80%----
|----20%----:#
{% endif %}
{% newline %}
| {{ acc.link }}
| {{ acc.value | currency_dc }}
{% assign wrong_accounts = wrong_accounts | plus:1 %}
{% endfor %}
{% endstripnewlines %}
{% endcapture %}
{% comment %}check on amount of "wrong accounts" and display the needed info{% endcomment %}
{% if wrong_accounts == 0 %}
{::infotext}
No wrong accounts are detected during this period {{ period.end_date | date:"%m-%d-%Y" }}
{:/infotext}
{% else %}
{::warningtext}
{% stripnewlines %}
{% if wrong_accounts == 1 %}
{{ wrong_accounts }} account has
{% else %}
{{ wrong_accounts }} accounts have
{% endif %}
been detected during the period {{ period.end_date | date:"%m-%d-%Y" }}:
{% endstripnewlines %}
{:/warningtext}
<br>
{% comment %}display the capture where the amount of wrong accounts was calculated in{% endcomment %}
{{ overview_wrong_accounts }}
{% endif %}
Is it possible to tune this template for a non cumul check?
So we can trace a non cumul booking?
We know that the account reverse holiday pay = credit account but now we can’t check if there is a debet booking on this account in the closing period because its a cumul check.
When I check on period 11 we see a huge amount Credit which is a cumul amount of january until november (in the case of reverse holiday pay) but mabye there is debet booking in this period.(november)
So eventually I am looking for a check for the mutation in the closing period. (for instance for november)
Could you mabye add it into the code below? I don’t know where to put it
{% comment %}create fixed range for debet accounts{% endcomment %}
{% assign debet_range = "40,6,41" %}
{% comment %}in the local variable "d_accounts" all debet accounts are taken{% endcomment %}
{% assign d_accounts = period.accounts | range:debet_range %}
{% comment %}we do the same for credit accounts{% endcomment %}
{% assign credit_range = "10,11,12,13,7" %}
{% assign c_accounts = period.accounts | range:credit_range %}
{% comment %}create empty string for d and c accounts - later on, this will be filled with ranges{% endcomment %}
{% assign d_range = "" %}
{% assign c_range = "" %}
{% comment %}loop over all D accounts and take the accounts out of it that have a value less than 0{% endcomment %}
{% for acc in d_accounts %}
{% if acc.value < 0 %}
{% comment %}each account that is less than 0, will be added to the string d_range, separated by a "$,"{% endcomment %}
{% assign d_range = d_range | append:acc.number | append:"$," %}
{% endif %}
{% endfor %}
{% comment %}all period accounts will be filtered on the accounts string we just created - the string that has only those accounts with a value less than 0{% endcomment %}
{% assign d_accounts = period.accounts | range:d_range %}
{% comment %}we do the same for the C accounts{% endcomment %}
{% for acc in c_accounts %}
{% if acc.value > 0 %}
{% assign c_range = c_range | append:acc.number | append:"$," %}
{% endif %}
{% endfor %}
{% assign c_accounts = period.accounts | range:c_range %}
{% comment %}Info box to let users know what ranges are taken{% endcomment %}
{::infotext}
Range controle debet-rekeningen: {{ debet_range }}
Range controle credit-rekeningen: {{ credit_range }}
{:/infotext}
* * *
{% comment %}create local variable and assign it to 0 - we will calculate with it later on{% endcomment %}
{% assign wrong_accounts = 0 %}
{% comment %}we capture this whole block of code because we need the value of how many accounts are wrong - we display that info first and then the whole block of coding (local variables are read from the top to the bottom){% endcomment %}
{% capture overview_wrong_accounts %}
{% stripnewlines %}
{% for acc in d_accounts %}{% comment %}looping over all wrong d-accounts{% endcomment %}
{% if forloop.first %}{% comment %}is there is at least one account, title will be shown{% endcomment %}
| Rekening met een credit bedrag die normaal een debet bedrag moeten hebben
{% newline %}
|:----100%----:#
{% newline %}
{% newline %}
| Account link
| Account value
{% newline %}
|----80%----
|----20%----:#
{% endif %}
{% newline %}
| {{ acc.link }}
| {{ acc.value | currency_dc }}
{% comment %}count how many wrong accounts there are - after the capture we will display that and then we display the capture{% endcomment %}
{% assign wrong_accounts = wrong_accounts | plus:1 %}
{% endfor %}
{% endstripnewlines %}
{% comment %}we do the same for the wrong C accounts{% endcomment %}
{% stripnewlines %}
{% for acc in c_accounts %}
{% if forloop.first %}
| Rekening met een debet bedrag die normaal een credit bedrag moeten hebben
{% newline %}
|:----100%----:#
{% newline %}
{% newline %}
| Account link
| Account value
{% newline %}
|----80%----
|----20%----:#
{% endif %}
{% newline %}
| {{ acc.link }}
| {{ acc.value | currency_dc }}
{% assign wrong_accounts = wrong_accounts | plus:1 %}
{% endfor %}
{% endstripnewlines %}
{% endcapture %}
{% comment %}check on amount of "wrong accounts" and display the needed info{% endcomment %}
{% if wrong_accounts == 0 %}
{::infotext}
No wrong accounts are detected during this period {{ period.end_date | date:"%m-%d-%Y" }}
{:/infotext}
{% else %}
{::warningtext}
{% stripnewlines %}
{% if wrong_accounts == 1 %}
{{ wrong_accounts }} account has
{% else %}
{{ wrong_accounts }} accounts have
{% endif %}
been detected during the period {{ period.end_date | date:"%m-%d-%Y" }}:
{% endstripnewlines %}
{:/warningtext}
<br>
{% comment %}display the capture where the amount of wrong accounts was calculated in{% endcomment %}
{{ overview_wrong_accounts }}
{% endif %}
This case needs to be adjusted then, where the collection with all accounts in it (period.accounts filtered in a specific range) needs to be period specific.
So you could do that by looping over the current period and previous period, like this:
{% comment %}in the local variable "d_accounts" all debet accounts are taken minus accounts of the previous period{% endcomment %}
{% assign d_accounts = period.accounts | range:debet_range %}
{% assign d_1p_accounts = period.minus_1p.accounts | range:debet_range %}
{% assign d_accounts = d_accounts-d_1p_accounts %}
{% comment %}we do the same for credit accounts{% endcomment %}
{% assign credit_range = "10,11,12,13,7" %}
{% assign c_accounts = period.accounts | range:credit_range %}
{% assign c_1p_accounts = period.minus_1p.accounts | range:credit_range %}
{% assign c_accounts = c_accounts-c_1p_accounts %}
All the rest of the code should work.
Haven’t gotten this question before where checks need to be done on a specific period
Let’s look at it differently (I’m complicating things I’m afraid).
Let’s build a simple case in which we show all accounts (with a range or not), and show the current value (YearToDate), the value of the previous period (also YTD but from the previous period) and calculate the PeriodToDate number
I’ve made this for you, which I hope you can build upon:
{% assign accounts = period.accounts.include_zeros %}
{% stripnewlines %}
| Number
| YTD value current period
| YTD value previous period
| PTD value
{% newline %}
|----20%----
|----30%----:
|----30%----:
|-----------:+
{% for acc in accounts %}
{% newline %}
| {{ acc.link }}
| {{ acc.value | currency }}
{% comment %}put the current account number in a var to use as a range later on{% endcomment %}
{% assign acc_nbr = acc.number %}
{% comment %}loop over all accounts again but filter with a range that equals the account nbr{% endcomment %}
{% assign prev_value = period.minus_1p.accounts.include_zeros | range:acc_nbr %}
| {{ prev_value | currency }}
{% comment %}calculate the PTD value of the account{% endcomment %}
| {{ acc.value-prev_value | currency }}
{% endfor %}
{% endstripnewlines %}
