Advanced Date Filtering Expression

elliot.serdet · 2025-01-22T10:25:41.943Z

Hello,

I have a challenging date filtering problem in Bubble.io that I’m trying to solve. I’m working with rental data and need to calculate the monthly rental cost based on the rented duration. Here’s the situation:

Data Structure:

Start Date: The start date of the rental period.
End Date: The end date of the rental period.
Cost per Month Rental: The monthly rental cost for the item.

Current Calculation:

I currently have an expression that works as follows:

Search for all rental records (i.e., all items rented out).
Calculate the total number of days between the End Date and Start Date.
Divide the total days by 30 to get the total number of months.
Multiply the total number of months by the Cost per Month Rental to get the total revenue for each item.

Here’s the Bubble.io expression that achieves this:

Do a search for all rental records
Format as text (this rental record’s End Date - this rental record’s Start Date, formatted as days / 30 * this rental record’s Cost per Month Rental)
Delimiter ,
Split by (,)
Each item converted to a number
SUM

This works for calculating total revenue based on the full rental period.

The Issue: Date Filtering

Now, I need to filter the rental period based on a “Date From” and “Date To” input, which complicates the calculation. Here’s the specific scenario:

If a rental record has a Start Date of 01/01/2025 and an End Date of 01/02/2025 (rented for 32 days total), but the Date From input is set to 10/01/2025, I want the calculation to consider only 22 days of rental (from 10/01/2025 to 01/02/2025) rather than the entire 32 days.

The Challenge:

How can I adjust the calculation so that the total rental days are filtered by the “Date From” and “Date To” input fields, ensuring the calculation reflects only the overlapping rental period between the given dates and the rental period in the database?

Any guidance or suggestions on how to implement this filtering logic would be greatly appreciated!

mitchbaylis · 2025-01-22T11:33:02.396Z

you’d have to dynamically calculate the rental total for each row based on the date filters and calculate it on the page rather than in the backend (assuming user is changing the filters often)

you’d be better to do it based on a daily rate also since months vary in days so a month with 31 days would be less per day than a month with 30 days (or 28)

to achieve this per row I’d add a few calculation groups on the page/row

days = end - start format as days (page level)
rental total = days x rental rate daily (row level) format as currency

now when user changes date filters it recalcs the days which then recalcs the rental total for each row

you may also need to calc days per row and use max/min date to compare filter date to the rental records date and use the appropriate date for start/end (ie if filter start > rental start then user rental start date)

elliot.serdet · 2025-01-22T11:52:09.814Z

Hello,

Thank you for your comment!

I did think of this option but got stumped when trying to make the calculation on a group.

I have set the group to type number and done a search for rental records, formatted as text.

But when doing the date minus calculation I am only allowed to use the Min or Max function on the first part of the argument.

For example

this items end date date input from - (not allowed to use here)

QIRO · 2025-01-22T12:05:13.908Z

I may be misunderstanding but if you’re doing a dynamic calculation based on the dates shown in the Repeating Group, that shouldn’t really rely on any complex filtering actions, should it?

The RG would search for records where the start date is (up to) 1 one billling month before the selected Start date, and ends before the End date.

I have done something similar in my application where I have a main RG that displays all records, and a hidden RG that only appears if the Date/Search input has a value (hiding the first RG).

Then in the hidden RG, calcuations are made, in your case, this would be the duration (in days) multipled but your daily rate to show your revenues. If the cost is always in months, you could just round it up. You could even use the selected Start and End dates as the inputs for your calculation, rather than the booking itself, reducing the complexity further.

elliot.serdet · 2025-01-22T13:10:10.307Z

Thank you for your comment. Instead of having these values displayed in a repeating group I am trying to create a summary of all the data displayed in one text element.

QIRO · 2025-01-22T17:50:08.269Z

What is the purpose of the summary?

If you just want to display the data the world is your oyster when it comes to options.

If you want to export it, then you can still do that with an RG.

system · 2025-04-02T10:25:44.248Z

This topic was automatically closed after 70 days. New replies are no longer allowed.