CASE: hide rows with zero amounts in them

If we created an overview of certain accounts for the current year and the previous year, we likely see some rows where there are no values. So it shows up as a zero.

We can adapt our code that these “zero-lines” will not be shown in an export, like this:


As you can see, the second row does not contain any number, so this should not be shown in the actual export (PDF).

How can we create such behaviour in our coding?

This is the code of our case:

{% comment %}check if previous year exists or not{% endcomment %}
{% assign previous_year_exists = period.minus_1y.exists %}
{% assign curr_y = period.year_end_date | date:"%Y" %}
{% if previous_year_exists %}
  {% assign prev_y = period.minus_1y.year_end_date | date:"%Y" %}
{% endif %}

{% comment %}create table depending on the fact if previous year exists or not{% endcomment %}
{% if previous_year_exists %}
{% capture header %}
|----65%----
|----15%----:
|----05%----
|----15%----:+
{% newline %}
| 
|_ {{ curr_y }} _
|
|_ {{ prev_y }} _
{% endcapture %}
{% else %}
{% capture header %}
|----85%----
|----15%----:+
{% newline %}
| 
|_ {{ curr_y }} _
{% endcapture %}
{% endif %}

{% comment %}create array with needed categories and their fixed account range; categories will be split on | while ranges need to be added with ";"{% endcomment %}
{% assign array_categories = "Costs goods type A;610__613|Costs goods type B;614|Costs goods type C;615|Costs goods type D;616" | split:"|" %}

{% comment %}<!------ TABLE ------->{% endcomment %}

{% stripnewlines %}
{{ header }}

{% comment %}user registers $1 for the current year and $2 for prev year in each loop; those values need to be reset for each loop after taking these values into registers $10 and $20 to display total value of table{% endcomment %}

{% for item in array_categories %}

    {% assign $1 = 0 %}{% assign $2 = 0 %}
    {% assign cat = item | split:";" %}
    {% assign name_cat = cat[0] %}
    {% assign value_cat = cat[1] %}
    {% $1+ period.accounts.[value_cat] %} 
    {% $2+ period.minus_1y.accounts.[value_cat] %} 
    {% comment %}in export, do not display a row if the values of current and previous year are BOTH zero{% endcomment %}
    {% assign hide_export = false %}
    
    {% unless $1 == 0 and $2 == 0 %} 
      {% assign hide_export = true %}
    {% endunless %}
      {% ifi hide_export %}
        {% newline %}
          | {{ name_cat }} 
          | {{ $1 | currency }}
          {% if previous_year_exists %}
            |
           | {{ $2 | currency }}
          {% endif %} 
      {% endifi %}
    {% $10+ $1 %}{% $20+ $2 %}
{% endfor %}

{% newline %}
{% comment %}show totals of each column, taken into registers $10 and $20{% endcomment %}
|
|_^ {{ $10 | currency }}  ^_
{% if previous_year_exists %}
|
|_^ {{ $20 | currency }}  ^_
{% endif %}
|
{% endstripnewlines %}

Let’s take a deeper look at what we do here.

{% comment %}check if previous year exists or not{% endcomment %}
{% assign previous_year_exists = period.minus_1y.exists %}
{% assign curr_y = period.year_end_date | date:"%Y" %}
{% if previous_year_exists %}
  {% assign prev_y = period.minus_1y.year_end_date | date:"%Y" %}
{% endif %}

This is done to trigger an extra column where the numbers of previous year has to be shown. But if there is no previous year, the extra column shouldn’t show at all. That’s why we check if the previous year exists or not and assign it to a local var, which we’ll use later on.

{% comment %}create table depending on the fact if previous year exists or not{% endcomment %}
{% if previous_year_exists %}
{% capture header %}
|----65%----
|----15%----:
|----05%----
|----15%----:+
{% newline %}
| 
|_ {{ curr_y }} _
|
|_ {{ prev_y }} _
{% endcapture %}
{% else %}
{% capture header %}
|----85%----
|----15%----:+
{% newline %}
| 
|_ {{ curr_y }} _
{% endcapture %}
{% endif %}

Depending on the fact whether or not the previous year exists, our table header needs to be configured differently. If we only can display one year, than the header only needs 2 columns, where as in the case a previous year exists, we’ll need more columns.
Keep in mind that the capture needs the newline-tags as well, because we use the outcome of the capture within stripnewlines.

{% comment %}create array with needed categories and their fixed account range; categories will be split on | while ranges need to be added with ";"{% endcomment %}
{% assign array_categories = "Costs goods type A;610__613|Costs goods type B;614|Costs goods type C;615|Costs goods type D;616" | split:"|" %} 

Instead of writing down code for each category, I’ll create an array which contains the name of each category, and the related account range for each category. We’ll see later on why we do this.

Now, we’ll actually start our table:

{{ header }} 

This will create the table header.

{% for item in array_categories %}

We’ll loop over our categories; in each loop we’ll do some logic to display the correct values.

{% assign $1 = 0 %}{% assign $2 = 0 %}

Registers $1 and $2 are used to take the values of the related ranges of each category, but because we use it in every loop (= category), we’ll assign these back to zero so it can be used in every loop (if you wouldn’t do this, each value of the loop would be added to the register, so we need to reset it).

{% assign cat = item | split:";" %}

Each loop displays the value out of the array in the var item but that’s not what we need because that actually has the name of the category as well as the range (f.i. in the first loop the output of the var item would be Costs goods type A;610__613.
But we need the name, which is the first part of the var item. So we’ll split on the “;” and assign that to a new var called cat so this can be done:

    {% assign name_cat = cat[0] %}
    {% assign value_cat = cat[1] %}

The var is now split in 2 parts (first the name, and second the range), so we can access the first part as cat[0] and assign it to a var called name_cat (the 0 looks at the first part).
Same goes for the second part: cat[1] and assign that to a var value_cat.

    {% $1+ period.accounts.[value_cat] %} 
    {% $2+ period.minus_1y.accounts.[value_cat] %} 

The dynamic var value_cat is now used as the needed range to display the value of those accounts. So if we do period.accounts.61 we’ll get the value of all 61-accounts. We can use our dynamic var as well, like this:

period.accounts.[value_cat] and period.minus_1y.accounts.[value_cat] for the previous year.

We’ll take the values of those and put them in the registers $1 (current year) and $2 (previous year), while not displaying them.

    {% assign hide_export = false %}
    
    {% unless $1 == 0 and $2 == 0 %} 
      {% assign hide_export = true %}
    {% endunless %}

We’ll create a var hide_export and assign it to false each loop. We do this because if we put this on true later in the loop, this “true” value will be taken into the next loop as well, which will cause incorrect behaviour: this var only can be put on true if the value of $1 AND $2 is zero:

    {% unless $1 == 0 and $2 == 0 %} 
      {% assign hide_export = true %}
    {% endunless %}

So if in the second loop, our if-statement is set on true (because $1 and $2 are zero there), the output of the var hide_export will stay true in the loops after the second, which isn’t correct. So we’ll set it on false each loop before we actually check the values of $1 and $2.

      {% ifi hide_export %}
        {% newline %}
          | {{ name_cat }} 
          | {{ $1 | currency }}
          {% if previous_year_exists %}
            |
           | {{ $2 | currency }}
          {% endif %} 
      {% endifi %} 

These are our columns for each row (loop), but the ifi-statement will cause a behaviour in the export that, when fulfilled (like in the second loop), it won’t show.
So an ifi-statement is the same as an if-statement, but that only has affect in the export mode!

    {% $10+ $1 %}{% $20+ $2 %} 

At the end of each loop, we’ll take the values of $1 and $2 in new registers $10 and $20, so these new registers can be used at the end of the table (remember: the registers $1 and $2 need to be reset in each loop to be used over and over again!).

{% newline %}
{% comment %}show totals of each column, taken into registers $10 and $20{% endcomment %}
|
|_^ {{ $10 | currency }}  ^_
{% if previous_year_exists %}
|
|_^ {{ $20 | currency }}  ^_
{% endif %}
|

Outside the forloop, we’ll display a last row that needs to display the end values of each column. So we need the value of registers $10 and $20 while respecting the view of our columns (which depend on whether or not previous year exists).

:bulb: Instead of creating lines of code for each separate category, we created an array to loop over. Why?

If we would’ve created a new line for each category, it would mean that for each line of code we would’ve needed to put the ifi-statement around it.
Now, we’ve created an array and only needed one ifi-statement. Less code! :ok_hand:
(if extra categories need to be added, then you simply have to add it into the array, and that’s it. If you wouldn’t have the array, you needed to create way lot more new lines of coding for those categories.

:bulb: Make the user decide whether or not zero-lines should be displayed on export

This could be done by creating a boolean a user has to check off. When checked, only then can the ifi-statement be executed for instance.

We make reconciliation of certain commissions for certain clients.
For bigger clients this reconciliation can get quite big.
Can we code this reconciliation that we can leave commissions with an amount of zero in order to use this data next year?
For example
2017:
commission A -> 100 euros
commission B -> 200 euros
2018:
commission A -> 200 euros
commission B -> 0 euros
commission C -> 100 euros
2019:
Commission A -> 100 euros
Commission B -> 100 euros

Is it possible to leave commission B in the reconciliation but not show it in the export?

{% addnewinputs %}
{% assign kosten_leverancier = current_account.custom.details | sort_natural:“Leverancier” %}
{% endaddnewinputs %}

{% stripnewlines %}
|Leverancier
|Omschrijving
|Periode
|Bedrag
{% newline %}
|—20%—
|—30%—
|—20%—
|—30%—:+
{% ifi hide_export %}
{% fori detail in kosten_leverancier %}
{% newline %}
|{% input detail.Leverancier %}
|{% input detail.Omschrijving %}
|{% input detail.Periode as:date %}-{% input detail.datum as:date %}
|{% $17+input detail.Bedrag as:currency %}
{% newline %}
|{% input detail.adres %}
|
|
|
{% newline %}
|{% input detail.postcode %}
|
|
|
{% newline %}
|{% input detail.BTWnummer %}
|
|
|
|
{% newline %}
|
|
|
|
{% endfori %}
{% endifi %}
{% endstripnewlines %}

{% comment %}reconcile value $17{% endcomment %}
{% unreconciled current_account.value-($17)%}

Hi Siel,

Thank you for your message. Yes, you can do so by using the ‘rollforward’ functionality in liquid, specifically ‘rollforward nil’. Please refer to the post below:

In order to hide code from export, you can enclose the information you want to hide within ‘ic’ tags. For example:

 {% ic %}
  | {% input detail.commission_b %}
 {% endic %}

Should you have any questions, please let us know.

Best,
Borja

Hi Borja

Thanks for your reply!

Can I use and if statement in combination with the ic tag?
That would already solve the problem.
We can already copy the details from last year. We just want to add lines year after year without deleting the empty lines. But in the export we only want to show the lines with values.

Best,
Siel

Hi Siel,

Yes, you can use the {% ifi %} tag in order to achieve that.

{% ifi variable != 0 %}
  Content
{% endifi %}

In the above example, the content will always be shown in input mode, however in export it will only show if the value of my variable is different than 0.

Hope that clarifies your query.

Best,
Borja