Calculate the sum of all transactions on a certain date
Good afternoon. There are two applications in the first one, transactions from customers are counted there are two fields, this is the transaction date and the transaction amount. In the second application, I need to make two calculations, the first to count the transaction amount for the current day, respectively, the information would be updated every day, and the second calculation that counted the amount of transactions for the current month and this calculation should be updated monthly.
For example, on 07.01.2023 I conducted 5 transactions in the amount of 2,000, and on 08.01.2023 I conducted 10 transactions in the amount of 5,000. Accordingly, in the first calculation, I should have had an amount of 2,000 yesterday, 5,000 today, and tomorrow, respectively, a new amount for the current date.
And in the calculation for the month there should be an amount of 7,000, and on 01.02.2023 it should become 0 because the month will change and there were no transactions in February yet. And when they appear, they will have to be counted.
I hope you will help me to cope with this task myself. I can't.
-
Hi Venom,
3 questions:
1. Are the 2 apps connected via a relationship field?
2. Do you want to see the results always in the same item in the second app (means: the app has one item only) or do you want/have items for each month?
3. Do you have a Podio Premium Account and use PWA (Podio Workflow Automation)? -
1. Yes. The first application is called "Payment Planning" it has the field amount, date and the field "Instrument" - this is just a related application. Also in this application there is a category field - "Completed" "Cancelled"
There are two calculation fields in the Tool app.
In the first one, you need to calculate the amount of the transaction from the status "Completed" for the selected instrument for the current day.
In the second calculation field, you need to calculate the sum of all transactions on the instrument for the month.
2. Both calculation fields where the result should be obtained are in the "Tool" application
3. Yes, we have Podio Premium and we use PWA. But I, since I am new to this, I think that we are not using it effectively. -
Hi Venom,
the main challenge is that the Podio calculation field doesn't know "today" (explanation and solution see my comment here ; that's where you need PWA).
After you have connected all items in the Tools app with the Timer app item (as described in my comment) you have the today's date available and you can compare the dates of the Payment items to the current day and month.
For the current day you can add the code:var today = @all of date field in timer app.toString();
today = moment(today).tz(your timezone here).format("YYYYMMDD");
var payment_date = @all of date field in payment app;
var payment_status = @all of category field in payment app;
var payment_amount = @all of amount field in payment app;
var sum = 0;
for(var i = 0; i < payment_date.length; i++){
var p_date = moment(payment_date[i]).tz(your timezone here).format("YYYYMMDD");
if(p_date == today && payment_status == "Completed"){
sum += payment_amount[i] || 0;
}
}
sumFor the current month you can copy the code and change the format() only a bit (just remove DD)
var today = @all of date field in timer app.toString();
today = moment(today).tz(your timezone here).format("YYYYMM");
var payment_date = @all of date field in payment app;
var payment_status = @all of category field in payment app;
var payment_amount = @all of amount field in payment app;
var sum = 0;
for(var i = 0; i < payment_date.length; i++){
var p_date = moment(payment_date[i]).tz(your timezone here).format("YYYYMM");
if(p_date == today && payment_status == "Completed"){
sum += payment_amount[i] || 0;
}
}
sumThe timezone must be in a format like ("Europe/Berlin") (that's my timezone) or ("America/New_York").
If you have any problems with the code share screenshots of the calculations fields, please.Rainer
-
Good afternoon, Rainer.
Thank you for your help. The formula is not working correctly. The condition is not being checked. I created a card and recorded a random number where the card was in the Created status. And the date on the card is March 23, 2023. But the formula still calculated it as an expense for today. I am attaching a screenshot.
Please sign in to leave a comment.
Comments
7 comments