CASE: group details of current and previous period / year

[sven]

Let’s say we want to build further on this case:

CASE: create fori and group on certain values

Let’s say we import a whole bunch of data through the automatic reconciliation function in a reconciliation template. To make it simple, I’ll import all parts of a car with their expenses from a certain excel-sheet I’ve gotten from my client, like this: [03] Because of all double types in the excel-sheet, I’d like to group these on type to see what amount correspond with what type. I’ll even try to make type Wheels (with capital) and wheels (without capital) the same type. To get something li…

but we’ll expand our overview of details on details of the current year AND the previous year (if it exists):

How you can access details from a previous period, is explained here:

CASE: display reconciliation of previous period

Let’s take this case: https://community.silverfin.com/t/case-create-fori-and-group-on-certain-values/1195?u=sven and say we want an option to display the same reconciliation from the previous year as well. [57] If you create your details into a custom collection, you can call upon these details by this code: period.minus_1y.reconciliations.name_handle.custom.collection where period.minus_1y is looking into not the current period, but the period one year back (so period.minus_1p works t…

Our complete code of our case:

{% assign current_year = period.year_end_date | date:"%Y" %}
{% assign prev_year = period.minus_1y.year_end_date | date:"%Y" %}
{% assign categories = "" %}

{% comment %}create var to check if prev_period exists{% endcomment %}
{% if period.minus_1y.exists %}
  {% assign prev_period = true %}
{% endif %}

{% comment %}create dynamic variables for the current year{% endcomment %}
{% for cat in custom.categories %}
  {% assign cat_name = cat.name %}
  {% capture cat_total %}total_{{cat_name}}_{{ current_year }}{% endcapture %}
  {% assign [cat_total] = [cat_total]+cat.value %}
  {% assign categories = categories | append:cat_name | append:";" %}
{% endfor %}

{% comment %}create dynamic variables for previous year IF it exists!{% endcomment %}
{% if prev_period %}
  {% for cat in period.minus_1y.reconciliations.recon_test.custom.categories %}
    {% assign cat_name = cat.name %}
    {% capture cat_total %}total_{{cat_name}}_{{ prev_year }}{% endcapture %}
    {% assign [cat_total] = [cat_total]+cat.value %}
    {% assign categories = categories | append:cat_name | append:";" %}
  {% endfor %}
{% endif %}

{% assign categories = categories | split:";" | uniq %}

<!-------- actual template ----------->

{% stripnewlines %}
| Categorie 
| {{ current_year }} {% if prev_period %}
| {{ prev_year }} {% endif %}
{% newline %}
|----
|---- {% if prev_period %}
|---- {% endif %}
# 
{% newline %}
{% for cat in categories %}
  {% capture cat_total_current %}total_{{cat}}_{{ current_year }}{% endcapture %}
  {% if prev_period %}
  {% capture cat_total_prev %}total_{{cat}}_{{ prev_year }}{% endcapture %}
  {% endif %}
| {{ cat }} 
| {{ [cat_total_current] | currency }} {% if prev_period %}
| {{ [cat_total_prev] | currency }}{% endif %}
{% newline %}
{% endfor %}
{% endstripnewlines %}

<br />

{% stripnewlines %}
| Categorie 
| Value 
{% newline %}
|----
|----# 
{% newline %}
{% fori cat in custom.categories %}
  | {% input cat.name %} 
  | {% input cat.value as:currency %} 
{% newline %}
{% endfori %}
{% endstripnewlines %} 

{% comment %}create var to check if prev_period exists{% endcomment %}
{% if period.minus_1y.exists %}
  {% assign prev_period = true %}
{% endif %}

This is to create a variable to display info IF a previous year exists (there’s no need to display a previous year if there is no data in that year - if the period does not exist)

{% comment %}create dynamic variables for the current year{% endcomment %}
{% for cat in custom.categories %}
{% assign cat_name = cat.name %}
{% capture cat_total %}total_{{cat_name}}_{{ current_year }}{% endcapture %}
{% assign [cat_total] = [cat_total]+cat.value %}
{% assign categories = categories | append:cat_name | append:“;” %}
{% endfor %}

{% comment %}create dynamic variables for previous year IF it exists!{% endcomment %}
{% if prev_period %}
{% for cat in period.minus_1y.reconciliations.recon_test.custom.categories %}
{% assign cat_name = cat.name %}
{% capture cat_total %}total_{{cat_name}}_{{ prev_year }}{% endcapture %}
{% assign [cat_total] = [cat_total]+cat.value %}
{% assign categories = categories | append:cat_name | append:“;” %}
{% endfor %}
{% endif %}

{% assign categories = categories | split:“;” | uniq %}

This is creating dynamic variables both for the current and the previous (if it exists) year.
So, we create dynamic variables where the name of the current year is saved and one where the previous year is saved (so 2 different dynamic variables for each categorie!)

{% stripnewlines %}
| Categorie
| {{ current_year }} {% if prev_period %}
| {{ prev_year }} {% endif %}
{% newline %}
|----
|---- {% if prev_period %}
|---- {% endif %}

{% newline %}
{% for cat in categories %}
{% capture cat_total_current %}total_{{cat}}{{ current_year }}{% endcapture %}
{% if prev_period %}
{% capture cat_total_prev %}total{{cat}}_{{ prev_year }}{% endcapture %}
{% endif %}
| {{ cat }}
| {{ [cat_total_current] | currency }} {% if prev_period %}
| {{ [cat_total_prev] | currency }}{% endif %}
{% newline %}
{% endfor %}
{% endstripnewlines %}

This is the actual overview where all categories are grouped for the current year and the previous year.

{% stripnewlines %}
| Categorie
| Value
{% newline %}
|----
|----#
{% newline %}
{% fori cat in custom.categories %}
| {% input cat.name %}
| {% input cat.value as:currency %}
{% newline %}
{% endfori %}
{% endstripnewlines %}

This is where the actual input of data happens (manually, or through automatic reconciliation with Excel for instance).

All credits to @Sam for his input on this