CMS Development

ben-duchy
Contributeur de premier rang

Filter data by date range

SOLVE

I’ve been tasked with displaying rows based on a set of date ranges with an overall number of how many rows meet this criteria. This date range will need to be editable within the page editor but for now I can't even get a hard coded version to work!

 

I thought something along these lines, but no luck!

 

 

{% set table = hubdb_table_rows(12345467) %}

{% for row in table %}
    {% set plot = unixtimestamp(row.plot_date) %}
    {% set startdate1 = "01/07/2021"|strtodate("dd/MM/yyyy") %}
    {% set enddate1 = "30/09/2021"|strtodate("dd/MM/yyyy") %}
  
    {% if plot | datetimeformat('%e/%m/%Y') >= startdate1 %}
      {% if plot | datetimeformat('%e/%m/%Y') <= enddate1 %}
      Display row data
      {% endif %}
    {% endif %}
  
  {% endfor %}

 

 

To summerise, it should show:

  • Between 1 July 2020 – 30 September 2020
    • Unsold Plots = 10
      • Row(s) information displayed
    • Reserved plots = 20
      • Row(s) information displayed
    • Exchanged plots = 25
      • Row(s) information displayed

Total plots = 55

 

Any help would be greatly appreciated.

0 Upvotes
1 Accepted solution
Indra
Solution
Guide | Diamond Partner
Guide | Diamond Partner

Filter data by date range

SOLVE

Hi @ben-duchy,

 

Did you check your previous question?
https://community.hubspot.com/t5/CMS-Development/Finding-the-length-of-repeating-dates/td-p/457184

 

It's best to format your date by numbers so you can compare if it's a larger of lower number. So best thing to do it to set your date up by %Y%m%d.

 

Your code will look like this:

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

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

{% for row in rows %}

  {% 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 }}

 


Bureau Vet

Did my post solve your question? Help the community by marking it as a solution

View solution in original post

ben-duchy
Contributeur de premier rang

Filter data by date range

SOLVE

My apologies, yes of course 🙂

Indra
Guide | Diamond Partner
Guide | Diamond Partner

Filter data by date range

SOLVE

@ben-duchy can you mark this topic as solved if this solved your problem?


Bureau Vet

Did my post solve your question? Help the community by marking it as a solution
0 Upvotes
Indra
Solution
Guide | Diamond Partner
Guide | Diamond Partner

Filter data by date range

SOLVE

Hi @ben-duchy,

 

Did you check your previous question?
https://community.hubspot.com/t5/CMS-Development/Finding-the-length-of-repeating-dates/td-p/457184

 

It's best to format your date by numbers so you can compare if it's a larger of lower number. So best thing to do it to set your date up by %Y%m%d.

 

Your code will look like this:

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

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

{% for row in rows %}

  {% 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 }}

 


Bureau Vet

Did my post solve your question? Help the community by marking it as a solution