I have a table which has 3 selection criteria:
Received/Receivable category (10 items to choose from)
Connected/Non Connected (2 items to choose from)
Country Code (239 items to choose from)
I need to be able to count the unique instances of the countries in each of the other two criteria. For instance, if the Category is Sale of Goods, Connected and Argentina has been selected for both lines, it should only count one country for the Sale of Goods Connected count.
However, if the Category is Sale of Goods Non Connected, it should count the country into that category.
In the screenshot attached, the result should be:
Sale of Goods Connected: 2
Insurance Premiums Connected: 1
Insurance Premiums Non Connected: 1
Bearing in mind that the total number of countries that can be selected is 239, so while a conditional count is possible, it’s probably not the best solution.
I’ve received an additional request re this functionality. How would I add the Transaction value for these items as well so that the output would have the count as well as a total value for those items:
You could extend the functionality provided by Robbe to accomplish that, with out the need of creating new loops or arrays. And the approach could be similar to what he has done to keep the count, but instead of doing a plus:1 every time, you keep track of the values filled in the inputs.
Here is an slightly modified version of the code, where I added the category_total variables.
{% comment %}array to keep track off all the chosen items{% endcomment %}
{% assign chosen_options_arr = "" | split:"" %}
{% comment %}array for all the categories that are selected{% endcomment %}
{% assign categories_arr = "" | split:"" %}
{% comment %}start table{% endcomment %}
{% stripnewlines %}
| Received / receivable
| Entity name
| Connected/non-connected
| Jurisdiction / Country code
| Value
{% newline %}
|--------
|--------
|--------
|--------
|--------#
{% fori item in custom.some_collection %}
{% newline %}
| {% input item.received as:select options:"Sale of goods|Insurance premiums" %}
| {% input item.entity_name placeholder:"Entity" %}
| {% input item.connected as:select options:"Connected|Non Connected" %}
| {% input item.country as:select options:"BE - Belgium|AR - Argentina|BD - Bangladesh" option_values:"BE|AR|BD" %}
| {% input item.value as:integer %}
{% if item.persisted %}
{% comment %}create a string of the item that was chosen, which can be used later{% endcomment %}
{% assign chosen_option = item.received | append:" " | append:item.connected | append:";" | append:item.country | append:";" | append:item.value %}
{% comment %}add the string to an array{% endcomment %}
{% push chosen_option to:chosen_options_arr %}
{% endif %}
{% endfori %}
{% endstripnewlines %}
{% comment %}apply uniq filter to array, to get every unique option that is chosen (including the country){% endcomment %}
{% assign chosen_options_arr = chosen_options_arr | uniq %}
{% comment %}loop over all the options{% endcomment %}
{% for item in chosen_options_arr %}
{% comment %}take the category name and add those to a new array, to count later{% endcomment %}
{% assign item_array = item | split:";" %}
{% assign category = item_array[0] %}
{% push category to:categories_arr %}
{% comment %}keep the sum of each category{% endcomment %}
{% assign item_value = item_array[2] %}
{% capture category_total %}{{ category }}_total{% endcapture %}
{% assign [category_total] = [category_total] | plus:item_value %}
{% endfor %}
{% comment %}loop over all the categories and use a dynamic variable to count the categories{% endcomment %}
{% for category in categories_arr %}
{% capture category_counter %}{{ category }}{% endcapture %}
{% assign [category_counter] = [category_counter] | plus:1 %}
{% endfor %}
{% comment %}get unique categories only{% endcomment %}
{% assign categories_arr = categories_arr | uniq %}
{% comment %}loop over the array with all the unique categories and display the counter{% endcomment %}
{% for category in categories_arr %}
{% capture category_counter %}{{ category }}{% endcapture %}
{% capture category_total %}{{ category }}_total{% endcapture %}
{{ category }} -- Count: {{ [category_counter] }} -- Total :{{ [category_total] }}
{% endfor %}
