How to do calculations with data for dashboard

Hi Forum,

How can i calculate a sum from a monthly period?

I have data with a timestamp and a value and i want to calculate KPIs with this. I havent found a good way that just delivers the monthly spending for example.

Steps

  1. Find the lowest value in one month.
  2. Find the highest value in one month (same month)
  3. Subtract end value from start value.

Bonus: Interpolate to have the exact value for 1st januaray and 31st january. (Linear interpolation)

Any ideas?

use the :sum operator in the dynamic expression

HI Boston,

That would work if i would want to sum all data. I just want to have the first and the last value of a month and then calculate the consumption.

Think of you home electricity meter.

On the begining of the month you already consumed 14 kWh (Start value) and at the end the meter is at 34 kWh. You have the start and the end value and many more datapoints arround those time and day.

Now i want to show you your consumption in that month. → End Value (34 kWh) - Start Value (14 kWh) = 20 kWh

So first i need to extract the start and end value from my dataset and then subtract them (subtraction is the easiest part here).

Do you know how i can do that?

You can do exactly that… i.e. just subtract the end value from the start value…

Are you saying it’s not working as expected? Or you don’t know how to do it?

Hi Adam,

I don’t know how to do that :sweat_smile:

Would you mind explaining it to me?

Would you mind explaining it to me?

Sure… it’s about as simple as it sounds (i.e. just subtract the lowest value from the highest value)…

But exactly how you do that depends on where and what your data is…

So what data are you actually using here?

Thanks a lot Adam!

I have a thing “Device” and a Thing “Device Values” (they are linked through a field)

The Device Values have a Timestamp (Date) and a Value (Number)

I want the user to select the device and a month (where there are values available) and then i want to know the first and the last value of the month.

Lets say its for january 23 so the user selects the device and then in a following dropdown the month january (also dont know how to show only available months in the dropdown)

Afterwards i want to display the consumption of the device for january so i need to know the first value i have in that month and the last value. The consumption then is the last subtracted by the first value.

Do you have everything you need?
Thank you so much!

@adamhholmes
if you also know how to calculate interpolated values you will save me weeks of research!

Then that’s simple…

Just take the first record from the month, and the last, and subtract the value of the first from that of the last.

And what operator do I use to do so? :sweat_smile:
(Sorry if that’s so too easy, I feel so deeply digged in the issue right now, I don’t get it)

Here’s how I would do it…

To make things simpler, I would load the first and last items into two groups first (used as variables, just to hold those items for reference later), and then simply deduct the value of the smaller number form the higher one.

So, add two groups somewhere to your page (it can be in a hidden popup, and/or it can have 0 height and width so it doesn’t take up any space on your page).

Set the content type of both groups to be ‘Device Value’.

In one of the groups, lets call it ‘Group Min Value’, load the first ‘Device Value’ entry of the month, by searching for Device Values, within the given month, sorting by the Date field (descending: No), and selecting the first item.

Then do the same for the other group, let’s call it ‘Group Max Value’, but this time loading the last Device Value of the month (sort by Date descending Yes and select the first item).

Then all you have to do is, wherever it is you need to do it, subtract the lower Device Value’s Value from the higher one by referring to Group Max's Value's Device Value's Value - Group Min's Value's Device Value's Value

(of course, you don’t have to use two groups to hold the data… you could just do those searches directly in your final expression, but it makes thing simpler to manage and edit, and means you only have to do it in one place, even if you need to refer to them in multiple places on your page).

1 Like

Thank you @adamhholmes

I dont know how to apply that. I do always get an error :frowning:

How would you calculate the values in the backend?
I would prefer a backend approach to have a simple as possible ui.

Any idea?

I dont know what i am doing wrong :frowning:

Thank you sooo much!

I do always get an error

What kind of error?

How would you calculate the values in the backend?
I would prefer a backend approach to have a simple as possible ui.

Any idea?

I’m not entirely sure what you mean…

but you can do the same thing on the backend if you’ve got some other datatype that you want to store the values on. Is that what you’re saying?

I’m not sure there’s any particular advantage to doing that though?..

If i get it correctly.

I need to create a group: :white_check_mark:
Then need to set the data type to “Number” or the data thing itself? (I have a thing Device Value with a Field Time and a field value. → What to chose?

What do i then need to fill in at “data source”?

Here’s a simple example:

Device Values | Bubble Editor

@adamhholmes Thank you, i now made it work but the reliance on the “first” and “last” value brings in some unnecessairy side effects and edge cases. The equation “breaks” if the timeframe only has one datapoint.

From my point of understanding it would be much easier to have a “Endpoint” that you can handle over a Device and a Timeframe or date and it will return the min and max or already the consumtion. (Thats how i would have done it in a normal code) is this possible in bubble as well?

I’m not sure I understand your question?.. (nor why the equation should ‘break’ with only 1 datapoint)…

But there are always multiple ways to do the same thing in Bubble (especially very simple things like this) so use whichever method best suits your needs.

@adamhholmes
How would you solve this in the backend?

How do i create typical Endpoints that i can “ask” for this consumption value? Or several Endpoints. One that delivers the Start Value of a Period, one delivers the End Value of a period.

Later then i will add interpolation to this to have a very accurate value.

Any Idea how to reate those endpoints?
The FE Solution would require to have the same code in many places (Component is difficult because they all need to be a little bit different in the FE)

Thanks already for you help till now.

Would also apreciate if you could explain how to do this with the “Min” and “Max” operator. This would eliminate sorting problems :smiley:

How would you solve this in the backend?

I’m still not entirely sure I know what you’re talking about (or what exactly it is that needs solving?)…

But if (for some reason) you want to do what I’ve shown above on the backend, then it’s no different than doing it on the front end (i.e. just establish the two values, and subtract one from the other - obviously you can’t store those values in groups, but you can refer to them in just the same way - or use a plugin like List Poppper & Friends to hold the values in ‘states’ on the backend).

To do this in Bubble you’ll need to set up a backend workflow, and then use the API Connector plugin to call it so you can retrieve the data from the backend workflow, back to your page.

I really don’t know why you’d want to go to all that trouble though for such a simple thing as this (when it can be done much simpler and faster directly on the page) but if you do then (as I said) the approach is no different.

Would also apreciate if you could explain how to do this with the “Min” and “Max” operator.

To use the Min or Max operators just apply them to any list of numbers (min will give you the lowest, and Max will give you the highest)

Thanks @adamhholmes

I think then i will do it in the FE :smiley:

I will try a few things out. Thank you so much!