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
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 
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?
(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).
Thank you @adamhholmes
I dont know how to apply that. I do always get an 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 dont know what i am doing wrong
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: 
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:
@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 
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
I will try a few things out. Thank you so much!
