We use cookies to make HubSpot's community a better place. Cookies help to provide a more personalized experience and relevant advertising for you, and web analytics for us. To learn more, and to see a full list of cookies we use, check out our Cookie Policy (baked goods not included).

- HubSpot Community
- HubSpot Developers
- CMS Development
- Re: Displaying 'length' of answers from a calculation

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Top Contributor

Jun 29, 2021 11:14 AM - edited Jun 29, 2021 11:20 AM

I've created a table that pulls in HubDB data between 2 date columns;

**{% set exchanged = unixtimestamp(row.exchange_date) %}**

**{% set reservation = unixtimestamp(row.reservation_date) %}**

**{% set exchanged_date = (exchanged - reservation)//24//60//60//1000 %}**

Above calculates the number of days between the 2 column entries.

**{% set total_weeks = (exchanged_date / 7 ) | round %}**

Then I use the number of days and round off to show how many weeks...

**{% for row in table %**}

Answer = **{{ total_weeks }}**

**{% endfor %}**

The above works, but now I want to display a bar graph that shows how many show 4 weeks, 5 weeks, 6 weeks etc. How do I calculate this? I tried the below but it didn't work:

**{% set test1 = (total_weeks, "equalto", "4") | length %}**

Any ideas would be appreciated

Solved! Go to Solution.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

1 Accepted solution

Key Advisor | Elite Partner

Jun 30, 2021 10:41 AM - edited Jun 30, 2021 10:47 AM

@ben-duchy You would want to push the calculated weeks into an array and then use selectattr.

```
{% set table = hubdb_table_rows(3958688) %}
{% set array = [] %} // create array variable
{% for row in table %}
{% set exchanged = unixtimestamp(row.exchange_date) %}
{% set reservation = unixtimestamp(row.reservation_date) %}
{% set exchanged_date = (exchanged - reservation)//24//60//60//1000 %}
{% set total_weeks = (exchanged_date / 7 ) | round %}
{% do array.append({"weeks": total_weeks}) %} // append number of weeks to array
{% endfor %}
{% set wks1 = array|selectattr("weeks","equalto","1")|length %} // find length with selectattr
{{ wks1 }}
```

And if you don't wanna write a whole bunch of sets for every possible number of weeks you could write a for loop with a range

```
{% for x in range(0,6) %}
{% set wks = array|selectattr("weeks","equalto",x)|length %}
{{ wks }}
{% endfor %}
```

And for the addition

```
{% set wks_total = {"total": "0"} %}
{% for x in range(-6,0) %}
{% set wks = array|selectattr("weeks","equalto",x)|length %}
{% set new_total = wks_total.total|int + wks|int %}
{% do wks_total.update({"total": new_total}) %}
{% endfor %}
{{ wks_total.total }}
```

*If this answer solved your question, please mark it as the solution!*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

4 Replies 4

Top Contributor

Jun 30, 2021 12:07 PM

Hi @amwilie, you're an absolute diamond! Thank you so much 😁

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Top Contributor

Jun 30, 2021 5:00 AM - edited Jun 30, 2021 5:09 AM

Hi @amwilie,

Good spot! You're correct that the loop does need to cover everything as you stated. This works if you want to display all the information inside a table (see below), but what I now need to do is calculate how may rows show 01 wks or 02 wks etc, another words...

- 01 wks = 1
- 03 wks = 1
- 04 wks = 5
- 05 wks = 2

If I was pulling data directly from HubDB, I would use the selectattr method for example:

**{% set table = hubdb_table_rows(xxxxxxx, queryparam) %}****{% set wks1 = table | selectattr ("weeks.name","equalto","one") | length %}**

**{% set wks2 = table | selectattr ("weeks.name","equalto","two") | length %}**

**{% set wks_total = (wks1 + wks2) %}**

The 2 numbers then get added together within my page using** {{wks_total}}**

Problem is that unlike my example above, the 'wks' column that I want to use doesn't exist in HubDB. The 'wks' column in the table above is a calculation between 2 HubDB columns, therefore how do I split a hubl calculation into more filtered data?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Key Advisor | Elite Partner

Jun 30, 2021 10:41 AM - edited Jun 30, 2021 10:47 AM

@ben-duchy You would want to push the calculated weeks into an array and then use selectattr.

```
{% set table = hubdb_table_rows(3958688) %}
{% set array = [] %} // create array variable
{% for row in table %}
{% set exchanged = unixtimestamp(row.exchange_date) %}
{% set reservation = unixtimestamp(row.reservation_date) %}
{% set exchanged_date = (exchanged - reservation)//24//60//60//1000 %}
{% set total_weeks = (exchanged_date / 7 ) | round %}
{% do array.append({"weeks": total_weeks}) %} // append number of weeks to array
{% endfor %}
{% set wks1 = array|selectattr("weeks","equalto","1")|length %} // find length with selectattr
{{ wks1 }}
```

And if you don't wanna write a whole bunch of sets for every possible number of weeks you could write a for loop with a range

```
{% for x in range(0,6) %}
{% set wks = array|selectattr("weeks","equalto",x)|length %}
{{ wks }}
{% endfor %}
```

And for the addition

```
{% set wks_total = {"total": "0"} %}
{% for x in range(-6,0) %}
{% set wks = array|selectattr("weeks","equalto",x)|length %}
{% set new_total = wks_total.total|int + wks|int %}
{% do wks_total.update({"total": new_total}) %}
{% endfor %}
{{ wks_total.total }}
```

*If this answer solved your question, please mark it as the solution!*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Key Advisor | Elite Partner

Jun 29, 2021 12:05 PM

Hey @ben-duchy

Don't know if this is your whole code so correct me if I'm wrong - but do you have the calculations inside the same for loop as where you're using {{ total_weeks }}? Because if not it's not going to be able to associate the {{ total_weeks }} with the calculations. This worked for me :

```
{% set table = hubdb_table_rows(XXXXXX) %}
{% for row in table %}
{% set exchanged = unixtimestamp(row.exchange_date) %}
{% set reservation = unixtimestamp(row.reservation_date) %}
{% set exchanged_date = (reservation - exchanged)//24//60//60//1000 %}
{% set total_weeks = (exchanged_date / 7 ) | round %}
Answer = {{ total_weeks }}
{% endfor %}
```

*If this answer solved your question, please mark it as the solution!*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Displaying 'length' of answers from a calculation

Developers

Jun 30, 2021