Sum search values dependant on dates

[dbenade]

Hi, I’m struggling to sum number values of my search criteria that are between dynamic dates.

I have two date values; statement_date that is provided via datepicker and fin_date is the record date (after testing this will change to record creation date)
I would like to sum the values of my search in the following brackets: 1-31, 32-61, 62-91, over 91 days old. This is for a statement app.

For 1-31 days I have the following search:

The other group dates I cannot approach with the same logic since it would include the former date group. I have tried to include <-range-> with no success.

[jason24]

Dates are a nightmare in bubble because you have to always remember to set the time.

From what I understand you want to firstly filter the sum from yesterday to 31 days ago?

[dbenade]

I would like to sum the search number values where fin_date is 32-61 days before statement_date.
If Statement_date is 05/30/2023 then a search number value with fin_date of 04/15/2023 would be in the bracket of 32-61 days and thus be included in the sum. But not a value with fin_date 05/01/2023 or 03/01/2023 because that would be in 1-31 and 62-91 day groups respectively.

[jason24]

I have an idea of how to solve your problem, I just need to know some context.

So you have 2 date pickers a statement date and a fin date, what is the fin date and statement date?

I am unsure of what you are trying to do/search, do you have a link to where you are at so far?

[dbenade]

Ok, so I have an invoice table that has records in that contains info like; customer, fin_date(invoice date) and amount (invoice amount) etc

I then have an input form with two inputs; dropdown of customers and a date picker input (which is passed to be the statement date) that generates a statement.
Inside the statement I have a repeating group showing all invoices pertained to the customer selected in the form’s dropdown.

Below the RG I have blocks dedicated to a group of dates for aggregation as explained earlier.
The fin_date is the date of invoice creation
The statement date is what the user selected in the form date picker. This should normally be the last day of current month but user selected none the less.

So when the invoice is dated 35 days (fin_date) before the statement date, said invoice amount should be in the total aggregation of 32-61 days. Below is a screenshot of the app

[jason24]

I thin I get you now, are the 4 different fields under:
Over 91 days
62-91 Days
32-61 Days
and the one you have done is 01-31 days, total due is all 4 totalled?

[dbenade]

That is correct. I cannot get the other to work.

[jason24]

What I would do is create a text input under each section and not text, this will then allow you to total it easier later.

Over 91 days:
you would do a search and add constraints:
fin date <= Statement date - 3 Months and then each items amount :sum

Let me know if this one works first and then we can discuss the others

[dbenade]

Yes working as expected.

[jason24]

Next you need to do

• 62-91 days:
  you would do a search and add constraints:
  fin date > Statement date - 3 Months
  fin date <= Statement date - 2 months
  and then each items amount :sum

• 32-61 days:
  you would do a search and add constraints:
  fin date > Statement date - 2 Months
  fin date <= Statement date - 1 months
  and then each items amount :sum

• 01-31 days:
  you would do a search and add constraints:
  fin date > Statement date - 1 Months
  fin date < Statement date
  and then each items amount :sum

For the total you would just do field 1 + field 2 + field 3 + field 4 formatted as currency/number

Let me know if this answers your question

[dbenade]

Thanks so much. This did the trick. I was so set on to create a single constraint that would do the entire job not thinking multiple would also do the trick.

[jason24]

Most of the time the simplest solution is staring you in the face, which happens a lot to me, I usually overcomplicate

I’m glad you have your solution anyway