Finding the length of repeating dates

Developer Support CMS Development
1

Is it possible to find how many rows share the same date?

I’ve craeted a simple if condition that displays true or false if the date is the month of July. What I now want to do is see how many rows are July, displayed as a number. Is this possible?

{% set test = hubdb_table_rows(1234567, "&plot_status__in=Completed&forecast_year__in=20/21") %}

{% for row in test %}
 {% set date = row.legal_completion|datetimeformat('%B') %}
 {% if row["legal_completion"] | datetimeformat('%B') == "July" %}
 true<br>
 {% else %}
 false<br>
 {% endif %}
{% endfor %}
2

Hi @ben-duchy,

You could loop through the items and update the values of a field. So you could setup your code like:

{% set test = hubdb_table_rows(1234567, "&plot_status__in=Completed&forecast_year__in=20/21") %}

{% set items_jul = {'amount' : 0 }%}

{% for row in test %}
 {% set date = row.legal_completion|datetimeformat('%B') %}
 {% if row["legal_completion"] | datetimeformat('%B') == "July" %}
 {% do items_jul.update({'amount' : items_jul.amount + 1}) %}
 {% endif %}
{% endfor %}

Items in July: {{ items_jul.amount }}

Let me know if this resolved your question?

3

Hi @Indra, Thank you very much, that’s spot on! :grinning_face:

Is there a way I could build on this so instead of looking at a fixed month, I could look at a date range for example 10th July - 15th September?

4

@ben-duchy,

You can. Just replace your if statement with a range from:

if row["legal_completion"] | datetimeformat('%B') == "July"

To

{% if from <= cur && cur <= till %}

So your code will look something like this:

{% set test = hubdb_table_rows(1234567, "&plot_status__in=Completed&forecast_year__in=20/21") %}

{% set items = {'amount' : 0 }%}

{% for row in test %}

 {% set cur = row["legal_completion"]|datetimeformat('%Y%m%d') %}
 {% set from = module.from_date|datetimeformat('%Y%m%d') %}
 {% set till = module.till_date|datetimeformat('%Y%m%d') %}

 {% if from <= cur && cur <= till %}
 {% do items.update({'amount' : items.amount + 1}) %}
 {% endif %}
{% endfor %}

Items in range: {{ items.amount }}
5

@Indra Thats fantastic!
I’ve spent a lot of time on this and couldn’t figure it out (hence my similar post) and you managed to do it within minutes.

Thank you so much again :+1: