New template "Bonus"

TinekedeKeyser · April 4, 2019, 9:59am

Hi,

I would lik to have an total of each person at the end of the year but it doesn’t work
Below you find my code

{% input custom.bonusemp.range as:account_collection range:“6” accounts_var:bonusemployees%}

{% stripnewlines %}

-----200%----

{% for acc in bonusemployees %}
{% newline %}
|{{ acc.link }}
|{{ acc.value | currency }}
{% newline %}
| Total
|^{{ bonusemployees.value | currency }}^
{% endfor %}
{% endstripnewlines %}

{% stripnewlines %}
| Name
| January
| February
| March
| April
| May
| June
| July
| August
| September
| October
| November
| December
| Total
{% newline %}
|----40%----
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|-----------:+
{% fori bonusemployees in company.custom.bonus %}
{% newline %}
| {% input bonusemployees.name as:text %}
| {% $0+input bonusemployees.bonusjan as:currency %}
| {% $1+input bonusemployees.bonusfeb as:currency %}
| {% $2+input bonusemployees.bonusmar as:currency %}
| {% $3+input bonusemployees.bonusapr as:currency %}
| {% $4+input bonusemployees.bonusmay as:currency %}
| {% $5+input bonusemployees.bonusjun as:currency %}
| {% $6+input bonusemployees.bonusjul as:currency %}
| {% $7+input bonusemployees.bonusaug as:currency %}
| {% $8+input bonusemployees.bonussep as:currency %}
| {% $9+input bonusemployees.bonusoct as:currency %}
| {% $10+input bonusemployees.bonusnov as:currency %}
| {% $11+input bonusemployees.bonusdec as:currency %}
{{+bonusemployees.bonusjan+bonusemployees.bonusfeb+bonusemployees.bonusmar+bonusemployees.bonusapr+bonusemployees.bonusmay+bonusemployees.bonusjun+bonusemployees.bonusjul+bonusemployees.bonusaug+bonusemployees.bonussep+bonusemployees.bonusoct+bonusemployees.bonusnov+bonusemployees.bonusdec}}
{% endfori %}
{% newline %}
|^^
|^ {{ $0 as:currency }}^
|^ {{ $1 as:currency }}^
|^ {{ $2 as:currency }}^
|^ {{ $3 as:currency }}^
|^ {{ $4 as:currency }}^
|^ {{ $5 as:currency }}^
|^ {{ $6 as:currency }}^
|^ {{ $7 as:currency }}^
|^ {{ $8 as:currency }}^
|^ {{ $9 as:currency }}^
|^ {{ $10 as:currency }}^
|^ {{ $11 as:currency }}^
|^ {{ $0+$1+$2+$3+$4+$5+$6+$7+$8+$9+$10+$11 }}^

{% endstripnewlines %}

You will see that there is now total in the vertical lines.
Can you check the liquid code please?

Regards,

Tineke

borjaGonzalez · April 4, 2019, 10:11am

Hi Tineke,

Welcome to Silverfin Community!

The horizontal total is not working as there is an extra ‘+’ at the beginning of your formula. It should be like this instead:

{{ bonusemployees.bonusjan+bonusemployees.bonusfeb+bonusemployees.bonusmar+bonusemployees.bonusapr+bonusemployees.bonusmay+bonusemployees.bonusjun+bonusemployees.bonusjul+bonusemployees.bonusaug+bonusemployees.bonussep+bonusemployees.bonusoct+bonusemployees.bonusnov+bonusemployees.bonusdec }}

Let me know you need any further assistance.

Regards,
Borja

TinekedeKeyser · April 4, 2019, 2:19pm

Thanks !

The unreconciled doesn’t work also

{% input custom.bonusemp.range as:account_collection range:“6” accounts_var:bonusemployees %}

{% stripnewlines %}

-----200%----

{% for acc in bonusemployees %}
{% newline %}
|{{ acc.link }}
|{{ acc.value | currency }}
{% newline %}
| Total
|^{{ bonusemployees.value | currency }}^
{% endfor %}
{% endstripnewlines %}

{% stripnewlines %}
| Name
| January
| February
| March
| April
| May
| June
| July
| August
| September
| October
| November
| December
| Total
{% newline %}
|----40%----
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|-----------:+
{% fori bonusemployees in company.custom.bonus %}
{% newline %}
| {% input bonusemployees.name as:text %}
| {% $0+input bonusemployees.bonusjan as:currency %}
| {% $1+input bonusemployees.bonusfeb as:currency %}
| {% $2+input bonusemployees.bonusmar as:currency %}
| {% $3+input bonusemployees.bonusapr as:currency %}
| {% $4+input bonusemployees.bonusmay as:currency %}
| {% $5+input bonusemployees.bonusjun as:currency %}
| {% $6+input bonusemployees.bonusjul as:currency %}
| {% $7+input bonusemployees.bonusaug as:currency %}
| {% $8+input bonusemployees.bonussep as:currency %}
| {% $9+input bonusemployees.bonusoct as:currency %}
| {% $10+input bonusemployees.bonusnov as:currency %}
| {% $11+input bonusemployees.bonusdec as:currency %}
| {{bonusemployees.bonusjan+bonusemployees.bonusfeb+bonusemployees.bonusmar+bonusemployees.bonusapr+bonusemployees.bonusmay+bonusemployees.bonusjun+bonusemployees.bonusjul+bonusemployees.bonusaug+bonusemployees.bonussep+bonusemployees.bonusoct+bonusemployees.bonusnov+bonusemployees.bonusdec }}
{% endfori %}
{% newline %}
|^^
|^ {{ $0 as:currency }}^
|^ {{ $1 as:currency }}^
|^ {{ $2 as:currency }}^
|^ {{ $3 as:currency }}^
|^ {{ $4 as:currency }}^
|^ {{ $5 as:currency }}^
|^ {{ $6 as:currency }}^
|^ {{ $7 as:currency }}^
|^ {{ $8 as:currency }}^
|^ {{ $9 as:currency }}^
|^ {{ $10 as:currency }}^
|^ {{ $11 as:currency }}^
|^ {{ $0+$1+$2+$3+$4+$5+$6+$7+$8+$9+$10+$11 }}^

{% endstripnewlines %}

{{ bonusemployees.value | currency }} {% unreconciled bonuesemployees.value-($0+$1+$2+$3+$4+$5+$6+$7+$8+$9+$10+$11) as:indicator %}

Kind regards,

Tineke

borjaGonzalez · April 4, 2019, 2:50pm

Hi Tineke,

Try adding the bonusemployees.value into a variable before you write your formula in your unreconciled tag. The following should work:

{% assign total = bonusemployees.value %}

{{ bonusemployees.value | currency }} {% unreconciled (total-($0+$1+$2+$3+$4+$5+$6+$7+$8+$9+$10+$11)) as:indicator %}

Best,
Borja

TinekedeKeyser · April 12, 2019, 9:08am

HI,

If the template is filled in, in month x+1, the month x+1 is also filled in in the template of month x. So the unreconciled of month x is red because of the month x+1 (what is not necessary for the month).

What is wrong with the template?

Kind regards
Tineke

Michiel · April 12, 2019, 9:41am

Hi Tineke,

If I understand your question correctly, you want to see all months up to the period you are in. So if you are in the case above in April you want to see January-February-March-April. Correct?

One thing you can try here is to use period.month_end_dates to define your columns (and column headers- (instead of fixed names “January, etc.”). For each column you will need to check if the column is in scope in that period:

{% for month in period.month_end_dates %} {% comment %}period.month_end_dates contains all months in your bookyear{% endcomment %}
  {% if month <= period.end_date %}
   {{ month | date:"%m/%Y" }}
  {% endif %}
{% endfor %}

This way the template can also handle broken bookyears.

I hope this helps!

Michiel

TinekedeKeyser · July 10, 2019, 12:16pm

Hi,

I have changed the input of the bonus template, but now he takes all months in 1 collum.
Can you please help me? Below you see the code.

Kind regards,

Tineke

{% input custom.bonusemp.range as:account_collection range:"6" accounts_var:bonusemployees %}

{% stripnewlines %}

{% for acc in bonusemployees %}
{% newline %}
|{{ acc.link }}
|{{ acc.value | currency }}
{% newline %}
| Total
|_^{{ bonusemployees.value | currency }}^_
{% endfor %}
{% endstripnewlines %}


<br>

{% stripnewlines %}
| Name
| {% for month in period.month_end_dates %} {% comment %}period.month_end_dates contains all months in your bookyear{% endcomment %}
  {% if month <= period.end_date %}
   {{ month | date:"%m/%Y" }}
  {% endif %}
{% endfor %}
| period.month_end_dates
| period.month_end_dates
| period.month_end_dates
| period.month_end_dates
| period.month_end_dates
| period.month_end_dates
| period.month_end_dates
| period.month_end_dates
| period.month_end_dates
| period.month_end_dates
| period.month_end_dates
| period.month_end_dates
| Total
{% newline %}
|----40%----
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|----10%----:
|-----------:+
{% fori bonusemployees in company.custom.bonus %}
{% newline %}
| {% input bonusemployees.name as:text  %}
| {% $0+input bonusemployees.bonusjan as:currency %}
| {% $1+input bonusemployees.bonusfeb as:currency %}
| {% $2+input bonusemployees.bonusmar as:currency %}
| {% $3+input bonusemployees.bonusapr as:currency %}
| {% $4+input bonusemployees.bonusmay as:currency %}
| {% $5+input bonusemployees.bonusjun as:currency %}
| {% $6+input bonusemployees.bonusjul as:currency %}
| {% $7+input bonusemployees.bonusaug as:currency %}
| {% $8+input bonusemployees.bonussep as:currency %}
| {% $9+input bonusemployees.bonusoct as:currency %}
| {% $10+input bonusemployees.bonusnov as:currency %}
| {% $11+input bonusemployees.bonusdec as:currency %}
| {{bonusemployees.bonusjan+bonusemployees.bonusfeb+bonusemployees.bonusmar+bonusemployees.bonusapr+bonusemployees.bonusmay+bonusemployees.bonusjun+bonusemployees.bonusjul+bonusemployees.bonusaug+bonusemployees.bonussep+bonusemployees.bonusoct+bonusemployees.bonusnov+bonusemployees.bonusdec }}
{% endfori %}
{% newline %}
|_^^_
|_^ {{ $0 as:currency }}^_
|_^ {{ $1 as:currency }}^_
|_^ {{ $2 as:currency }}^_
|_^ {{ $3 as:currency }}^_
|_^ {{ $4 as:currency }}^_
|_^ {{ $5 as:currency }}^_
|_^ {{ $6 as:currency }}^_
|_^ {{ $7 as:currency }}^_
|_^ {{ $8 as:currency }}^_
|_^ {{ $9 as:currency }}^_
|_^ {{ $10 as:currency }}^_
|_^ {{ $11 as:currency }}^_
|_^ {{ $0+$1+$2+$3+$4+$5+$6+$7+$8+$9+$10+$11 }}^_

{% endstripnewlines %}

<br>


{% assign total = bonusemployees.value %}

{{ bonusemployees.value | currency }} {% unreconciled (total-($0+$1+$2+$3+$4+$5+$6+$7+$8+$9+$10+$11)) as:indicator %}

sven · July 10, 2019, 12:42pm

Hello @TinekedeKeyser,

I’ve tweaked your code a little bit, and actually, it is rather a more complicated template, as you’ll need to loop over your period.month_end_dates to create columns.
eg if you only have 3 months, you need to create only 3 input database variables (and not all 12).

Here’s just an example:

{% input custom.bonusemp.range as:account_collection range:"6" accounts_var:bonusemployees %}

{% stripnewlines %}

{% for acc in bonusemployees %}
{% newline %}
|{{ acc.link }}
|{{ acc.value | currency }}
{% newline %}
| Total
|_^{{ bonusemployees.value | currency }}^_
{% endfor %}
{% endstripnewlines %}


<br>

{% stripnewlines %}
| Name
  {% for month in period.month_end_dates %} {% comment %}period.month_end_dates contains all months in your bookyear{% endcomment %}
    {% if month <= period.end_date %}
     |  {{ month | date:"%m/%Y" }}
    {% endif %}
  {% endfor %}
| Total
{% newline %}
|----40%----
{% for month in period.month_end_dates %}
{% if month <= period.end_date %}
|-------
{% endif %}
{% endfor %}
|-----------:+
{% fori bonusemployees in company.custom.bonus %}
{% newline %}
| {% input bonusemployees.name as:text  %}
  {% assign $0 = 0 %} {% comment %}reset register so it can be used for each loop{% endcomment %}
{% for month in period.month_end_dates %}
{% if month <= period.end_date %}
  {% comment %}use the name of the local var month as part of your database variable{% endcomment %}
  {% assign month_name = month | date:"%m" %}
  {% capture month_id %}month_{{ month_name }}{% endcapture %}  {% comment %}we will use this as a dynamic variable{% endcomment %}
  | {% $0+input bonusemployees.[month_id] as:currency %}
{% endif %}
{% endfor %}
| **{{ $0 | currency }}** 
{% endfori %}
{% endstripnewlines %}

does this makes any sense to you, as it is somewhat more complicated as you have a for-loop within a fori.

Could you try to update your template with this logic?

Happy to help

TinekedeKeyser · July 10, 2019, 1:24pm

Hi Sven,

Thanks!
That look well! Just the triangel in the end is green and that need te be red .
Can you look for that part?

Kind regards,

Tineke

{% input custom.rent.range as:account_collection range:“6” accounts_var:renting %}

{% stripnewlines %}

{% for acc in renting %}
{% newline %}
|{{ acc.link }}
|{{ acc.value | currency }}
{% newline %}
| Total
|^{{ renting.value | currency }}^
{% endfor %}
{% endstripnewlines %}

{% stripnewlines %}
| Name
{% for month in period.month_end_dates %} {% comment %}period.month_end_dates contains all months in your bookyear{% endcomment %}
{% if month <= period.end_date %}
| {{ month | date:"%m/%Y" }}
{% endif %}
{% endfor %}
| Total
{% newline %}
|----40%----
{% for month in period.month_end_dates %}
{% if month <= period.end_date %}
|-------
{% endif %}
{% endfor %}
|-----------:+
{% fori renting in company.custom.rent %}
{% newline %}
| {% input renting.name as:text %}
{% assign $0 = 0 %} {% comment %}reset register so it can be used for each loop{% endcomment %}
{% for month in period.month_end_dates %}
{% if month <= period.end_date %}
{% comment %}use the name of the local var month as part of your database variable{% endcomment %}
{% assign month_name = month | date:"%m" %}
{% capture month_id %}month_{{ month_name }}{% endcapture %} {% comment %}we will use this as a dynamic variable{% endcomment %}
| {% $0+input renting.[month_id] as:currency %}
{% endif %}
{% endfor %}
| {{ $0 | currency }}
{% endfori %}

{% endstripnewlines %}

{% assign total = renting.value %}

{{ renting.value | currency }} {% unreconciled (total-($0+input renting.[month_id])) as:indicator %}

Kimberly_Vlietinck · July 11, 2019, 8:10am

Hi @TinekedeKeyser

I’ve changed your code to fix your unreconciled-tag and I have added a new register $1 to add up the total in the last column of each row.

Renting.value is then being checked against the value of $1 in your unreconciled-tag.

Here you can find the entire code with my tweaks:

{% input custom.rent.range as:account_collection range:"6" accounts_var:renting %}

{% stripnewlines %}

{% for acc in renting %}
{% newline %}
|{{ acc.link }}
|{{ acc.value | currency }}
{% newline %}
| Total
|^{{ renting.value | currency }}^
{% endfor %}
{% endstripnewlines %}


{% stripnewlines %}
| Name
{% for month in period.month_end_dates %} {% comment %}period.month_end_dates contains all months in your bookyear{% endcomment %}
{% if month <= period.end_date %}
| {{ month | date:"%m/%Y" }}
{% endif %}
{% endfor %}
| Total
{% newline %}
|----40%----
{% for month in period.month_end_dates %}
{% if month <= period.end_date %}
|-------
{% endif %}
{% endfor %}
|-----------:+
{% fori renting in company.custom.rent %}
{% newline %}
| {% input renting.name as:text %}
{% assign $0 = 0 %} {% comment %}reset register so it can be used for each loop{% endcomment %}
{% for month in period.month_end_dates %}
{% if month <= period.end_date %}
{% comment %}use the name of the local var month as part of your database variable{% endcomment %}
{% assign month_name = month | date:"%m" %}
{% capture month_id %}month_{{ month_name }}{% endcapture %} {% comment %}we will use this as a dynamic variable{% endcomment %}
| {% $0+input renting.[month_id] as:currency %}
{% endif %}
{% endfor %}
| {% =$1+ $0 | currency %}
{% endfori %}

{% endstripnewlines %}


{% assign total = renting.value %}

{{ renting.value | currency }} {% unreconciled (total-$1) as:indicator %}

Kind regards,
Kimberly

TinekedeKeyser · July 11, 2019, 11:46am

thanks!

TinekedeKeyser · July 11, 2019, 12:48pm

Hi,

Is it possible to make a total under each month also?

Kind regards,

Tineke

Thomas_Thomsen · July 11, 2019, 2:26pm

Hi Tineke
Yes it is certainly possible, you can for example do it like this.

{% input custom.rent.range as:account_collection range:"6" accounts_var:renting %}
{% stripnewlines %}
{% for acc in renting %}
{% newline %}
|{{ acc.link }}
|{{ acc.value | currency }}
{% newline %}
| Total
|^{{ renting.value | currency }}^
{% endfor %}
{% endstripnewlines %}
{% stripnewlines %}
| Name
{% for month in period.month_end_dates %} {% comment %}period.month_end_dates contains all months in your bookyear{% endcomment %}
{% if month <= period.end_date %}
| {{ month | date:"%m/%Y" }}
{% endif %}
{% endfor %}
| Total
{% newline %}
|----40%----
{% for month in period.month_end_dates %}
{% if month <= period.end_date %}
|-------
{% endif %}
{% endfor %}
|-----------:+
{% fori renting in company.custom.rent %}
{% newline %}
| {% input renting.name as:text %}
{% assign $0 = 0 %} {% comment %}reset register so it can be used for each loop{% endcomment %}
{% for month in period.month_end_dates %}
{% if month <= period.end_date %}
{% comment %}use the name of the local var month as part of your database variable{% endcomment %}
{% assign month_name = month | date:"%m" %}
{% capture month_id %}month_{{ month_name }}{% endcapture %} {% comment %}we will use this as a dynamic variable{% endcomment %}
{% capture month_total %}total_{{ month_name }}{% endcapture %}
| {% $0+input renting.[month_id] as:currency %}
{% assign [month_total] = [month_total]+renting.[month_id] %}
{% endif %}
{% endfor %}
| {% =$1+ $0 | currency %}
{% endfori %}
{% newline %}
|Total
{% for month in period.month_end_dates %}
{% if month <= period.end_date %}
  {% assign month_name = month | date:"%m" %}
  {% capture month_total %}total_{{ month_name }}{% endcapture %}
  |{{ [month_total] | currency }}
{% endif %}
{% endfor %}
|{{ $1 | currency }}
{% newline %}
{% endstripnewlines %}
{% assign total = renting.value %}
{{ renting.value | currency }} {% unreconciled (total-$1) as:indicator %}

Topic		Replies	Views
Sub totals per Year Templates customized	9	477	June 19, 2018
New account model Templates custom_template	10	402	January 31, 2023
Beroepskosten - totals Templates variables , math , register	7	548	June 6, 2018
Account template (42) Templates styling	4	713	March 26, 2019
Template is not showing all selected accounts Templates	4	209	January 2, 2024

New template "Bonus"

Related topics