Calculate Cumulative Cost By Month
I don't know if this is possible, but I would like to see a monthly cumulative cost. So in the app I would have a Day Cost and the Calculation would reach out across my app items within the same App and pull other entries matching the year/month and add those entries cost numbers together plus the current entry. It sounds easy in theory, but having difficulties finding the right javascript to reach out to the other previous app items and sum them together. Podio gives a great "total tally". I understand that I can do this in the reports, but for this purpose I really need it as an app item calculation.
-
Hi Nathan,
look here how to get the values across your app items within the same App
https://help.podio.com/hc/communities/public/questions/202556448-Make-a-calculation-using-a-value-from-previous-entry-in-the-same-appAfter you have connected all your items you can use this formula to get the value which match the same year/month:
var dateMonthCurrent = moment(@DateFieldInYourApp).format('YYYY-MM'); // for getting the month of the current item var dateYearCurrent = moment(@DateFieldInYourApp).format('YYYY'); // for getting the year of the current item var dayCostCurrent = @NumberFieldWithValue; // for getting the the cost value of the current item var dateAll = @all of @DateFieldInYourApp; // for getting the datesof all other items var dayCostAll = @all of NumberFieldWithValue; // for getting the cost values of all other items //Calculation for month totalMonth = 0 for (var i = 0; i < dayCostAll.length; i++) { if(moment(dateAll[i]).format('YYYY-MM') == dateMonthCurrent){ totalMonth +=dayCostAll[i] } }; // Calculation for Year totalYear = 0 for (var i = 0; i < dayCostAll.length; i++) { if(moment(dateAll[i]).format('YYYY') == dateYearCurrent){ totalYear +=dayCostAll[i] } }; "Total " + dateMonthCurrent + ": " + (totalMonth + dayCostCurrent) + "\n" + "Total + dateYearCurrent + ": " + (totalYear + dayCostCurrent)Result looks like:
Total 2015-02: 10,000
Total 2015: 30,000If you want total month and total year in 2 fields put each calculation in another field.
Rainer
rg@delos-consulting.com -
This is great Rainer, I appreciate this a bunch. Just to run down what we are doing is we have 18 or so locations that all web form so I'll definitely have to lookup the location in addition to the date times as specified below. This gives us a great start though, looking forward to using your info!
-
Do you need an own calculation field for each calculation )so that you can use it for further calculations) or do you need the calculation field as a place for a report e.g. like a list of all location as a table:
Location Month Year
Location1 10,000 30,000
Location2 8,000 32,000
.....Rainer
-
Hi Rainer, great stuff! But I don't get one thing... here's my script:
var Current = @Number,
ArrPrev = @All of Number,
LengthPrev = ArrPrev.length ,
TotalPrev = 0 ;for (var i = 0; i < LengthPrev; i++) {
TotalPrev += ArrPrev[i]
};Current + TotalPrev
ArrPrev.length always returns 1 - so I do collect the number from the previous item, but not from the ones before. No cumulation over more than two items. How to fix this?
-
Hi Fritz,
I'm not sure if I understand you right. If ArrPrev.length always = 1 then there is only 1 prev item added to the item where you want to do the calculation.
If you want the total of all previous items you need to connect all previous to the current item. F.ex.: If you have 5 items it should look like this:
item 5 has relations to the items 4,3,2,1
item 4 to 3,2,1
item 3 to 2,1
item 2 to 1You can use Globiflow for that: When a new item is created, search for previous items and update current item in a relation field with relation to each previous.
It seems, you want simple the sum of all previous items' number fields. If so you don't need a for-loop, you simply need
@Sum of number field
Take care to select the right @Sum-token, cause you've an incoming and an outgoing relation to other items of this app.
Hope this helps,
Rainer
rg@delos-consulting.com -
Hi Rainer,
that's exactly what's going on. I thought maybe there's a way to "climb through the chain" or so.
Thank you for the globiflow tip; otherwise this would be really cumbersome (I'd like to calculate cumulative cash flow per week, so I have about 52 items per year. Pretty long list)
All the best,
Fritz
-
Hi Fritz,
another way is, to create a "Report"-App with only one item. Then link each weekly item to this report item (can be automated by Globiflow). In a calculation field you can create a result for each week (maybe as a table). From this calculation field you can pull back the result for each week into each weekly item.
Rainer
-
That's a smarter way indeed.
+ In the report app create one item per year (so you can call the app "years") and link every week item of that year to it (no need for GF in fact if you make a view filtering weeks of the respective year, then you just have to click on 52 items to add them, 20 seconds). + Then in the weeks app create a new ID field combining year and week, e.g. 1628 for this week (no. 26 of 2016)
+ Then in the years app sum up create 52 fields which sum up the cash numbers for the appropriate IDs. Maybe by determining the positions of the appropriate weeks in the cash number array and adding up the respective elements
+ Create a one item app to connect the years, to pass the week 52 number to week 1 of the following year.Unfortunately I've no time right now to make it happen... for the moment I'm happy with one year, which I did already with the "old" method of linking week items. I'll get back here later!
-
It's not a matter of the "20 Seconds" to use Globiflow, but a matter of "not forgetting" to link a new week item. I would use a flow: If a new week item is created, link it to the Report app item.
In the report app you don't need items for every year, it works with one item. With some simple Javascript you can create nice overviews in only one or - if needed - in multiple calculation fields: As a table with lines for each week or month or quarter or year - or as a total sum.Rainer
Please sign in to leave a comment.
Comments
9 comments