topic Re: Filter data by date range in CMS Development

Filter data by date range

ben-duchy — Mon, 12 Jul 2021 11:21:07 GMT

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.

Re: Filter data by date range

Indra — Tue, 13 Jul 2021 06:25:46 GMT

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

Re: Filter data by date range

Indra — Tue, 13 Jul 2021 12:56:35 GMT

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

Re: Filter data by date range

ben-duchy — Tue, 13 Jul 2021 13:05:21 GMT

My apologies, yes of course 🙂