Grouped By Day - Last Item

twofatthumbs · 2024-07-10T21:22:39.678Z

Hi

I am hoping for some guidance as I find myself stuck with the following:

I have a DataType that contains multiple values for a day and the fields Date, Total Sales, and Average Sales.

I want to access the Total Sales and Average Sales values for a date range to be able to run calculations on such as SUM, AVERAGE etc.

I have been able to use Grouped By to group the data by day and aggregating by Created Date MAX to get the last row of the day:

image364×584 13.8 KB

The issue I am having, though, is how to access the Total Sales and Average Sales data from the last row to use in the expression. We have tried running an Aggregation on these fields using MAX. However, the MAX value isn’t always the last value, so it doesn’t work in this situation.

Hopefully, it makes sense, and any insights are greatly appreciated.

TC

bonjour_17 · 2024-07-12T08:43:26.212Z

Hi, I am having the feeling that there is only 1 object per day, so do you really need grouping? Cannot you work on a list of objects if there is only 1 per day?

If there are multiple objects per day, then indeed you’ll need grouping. In that case, can you tell a bit more of what you need to achieve with the data?

twofatthumbs · 2024-07-14T07:27:26.794Z

Thanks for the insights. We fall into the latter camp of having multiple entries across a day.

Currently, we record an entry every hour with cumulative totals for the day. The very last entry contains the totals for the full day, and these are the values we want to access.

I can create a GroupBy that groups by day and outputs the data for those days; however, it outputs all of the entries for that day rather than the last.

For example, I am trying to show a SUM of TotalSales Day 1, TotalSales Day 2, etc. As the value at the end of the day may not be the maximum value, I can’t use MAX.

Thanks again, and hopefully, there is a simple way forward that I am missing.

gerbertdelangen · 2024-07-14T08:47:52.727Z

Hey T, to display a sum per day you don’t really need a totals line. You can use an ‘aggregation’:

image884×728 62.6 KB

twofatthumbs · 2024-07-14T09:48:37.855Z

Hi @gerbertdelangen

I really appreciate the insights. We had tried this, but we ran into the issue that when adding the Data source to the Grouping, it forces a first- or last-item requirement.

As a result, we only get the first day or the last days value:

image714×590 27.2 KB

Again, I suspect there is something obvious we are missing as the aggregation seems to be the right way to handle and will allow for calculating the average as well on required fields.

Thanks again and appreciate any further insights you may have as to where we are going wrong.

-T

gerbertdelangen · 2024-07-14T10:47:06.671Z

Is it possible to remove ‘first item’ from the search? I also remember it being added, but it was possible to remove it (maybe remove it and refresh the page to get rid of the error message).

georgecollier · 2024-07-14T10:58:12.589Z

It requires first item or last item because you’re putting it into a group: Repeating groups handle lists.

You should have a Sale data type that stores a sale’s date, total, etc.

Search for Sales:grouped by date with aggregations of sum and average on the order total will return the total and average for each day.

To get the total over a period of time, reference the above expression:filtered (date > minimum date and date < maximum um date):each item’s sum:sum (this is the sum of each day’s total).

gerbertdelangen · 2024-07-14T11:13:13.214Z

Ah, sharp @georgecollier.

@twofatthumbs, see george’s response above. You are using a group, I was using a repeating group. The question is, do you want to show a list of days with a total per day, if so, use a repeating group, see the images below. Otherwise what George wrote.

image786×334 5.74 KB

In the editor it looks like this:

image972×337 18 KB

twofatthumbs · 2024-07-14T18:54:42.628Z

Thanks @georgecollier and @gerbertdelangen

As we are looking for a combined total across the date range, there is no need for a repeating group, so your approach @georgecollier is perfect.

image702×589 26.5 KB

The issue we keep running into is that we have multiple entries for each day, and we only want to take the last entry, as this has the cumulative totals in it. Currently, the above expression sums all total_sales for each day.

Day 1
entry morning
entry afternoon
entry evening ← we want to this entry

Day 2
entry morning
entry afternoon
entry evening ← we want to this entry

Day 3
entry morning
entry afternoon
entry evening ← we want to this entry

Sum of total_sales = Day1Evening + Day2Evening + Day3Evening

Thanks again for the help and insights - very much appreciated!

-T

georgecollier · 2024-07-14T19:50:03.210Z

twofatthumbs:

we only want to take the last entry, as this has the cumulative totals in it.

Why? You can get the cumulative total for each day. What exactly is your use case - why don’t you have a Sale / Order data type and are instead calculating cumulative totals throughout three points in the day?

twofatthumbs · 2024-07-14T21:43:27.451Z

Thanks @georgecollier

We create a record every hour with the cumulative totals throughout the day. We use this to provide more granular analytics data for clients and already receive some of the data from Meta as cumulative totals.

Thinking further on this, a not-so-elegant workaround is to create another Data Type, sales_data_totals, and create a single record for each day with the totals. This will negate the need to use a Group By, and we can just run SUM / AVERAGE on the date range.

Again, I appreciate the insights and help as we work through options.

-T

georgecollier · 2024-07-15T04:31:55.730Z

twofatthumbs:

We create a record every hour with the cumulative totals throughout the day. We use this to provide more granular analytics data for clients and already receive some of the data from Meta as cumulative totals.

This just isn’t reliable though - what about when someone edits a past order’s value, for example? You will really find it better to calculate it on the fly using :group by and aggregations

gerbertdelangen · 2024-07-15T08:12:27.416Z

Hey @twofatthumbs and @georgecollier,

Agree with George about calculating stuff on the fly. But if you have to have totals, you can also add an isTotal (yes/no field) to the sale data type. When a new total (or total of totals ) is added/calclated that is a day total, set the flag to yes and use that in the search, no ‘group by’ needed anymore:

image917×585 40.3 KB

Tried the advanced filter and that also seems to work (assuming you only want the entries created in the evening and the evening starts at 6 pm):

image910×418 27.2 KB

image455×531 17.2 KB

The downside is that it brings all of the entries for the selected period to the client first.

twofatthumbs · 2024-07-15T09:18:54.214Z

Thanks @georgecollier and @gerbertdelangen

I appreciate that the current setup is not ideal or efficient. However, as this is an MVP, it gets us where we need to go for now.

@gerbertdelangen, your solution using the ‘yes’/‘no’ field is genius - thank you!

-T

georgecollier · 2024-07-15T09:35:37.925Z

twofatthumbs:

However, as this is an MVP, it gets us where we need to go for now.

Yep I get it, but calculating on the fly is quicker and easier than trying to have a synced data type in a recurring backend workflow that calculates cumulative totals Why have all that going on when you can have it all in one expression!

gerbertdelangen · 2024-07-15T11:30:58.828Z

Thanks man, but not really true . It is just that with Bubble there are always many roads to Rome . And just like MVPs, also large scale apps like ERPs are possible (plus Josh mentioned last year to also focus on Enterprise, so more related features should be coming).

About updating the totals table, maybe triggers are the way to go.