Sum Monthly Revenue based on Date Fields
I have a Sales App (commission date start, commission date end, commission MODE (monthly, semiannual, etc), and commission date amount) and a P&L App (Reporting Period, Revenue Auto Calculation) . The following calculation is returning "0" when it should be an amount >0. I have screenshots attached. Thank you! 
-
Hi Mike,
is there always only one Sales App item related to the P&L-item where you want to calculate the revenue or shall it calculate the revenue from multiple Sales items? For both options your code is wrong.
Btw: The line var datei doesn't make sense there. Take it out of the loop, remove [i] and add it to the other vars:
var date = @Reporting PeriodRainer
-
Hello Mike,
I've asked if there's one or multiple items related, cause that determines the vars start and and. If multiplie (like in your case) you can't use @max of date - cause this pulls the max date of all items (3 items with dates 09/08/2107, 09/09/2017, 09/10/2017 -> max = 09/10/2017). But what you want to get, is start and end of each item to compare each item to the Reporting date. This said you need
var amt = @All of Comission Data Amount;
var start = @All of Comission Start;
var end = @All of Comission End;
var date = moment(@Reporting Period);
var sum = 0;
for(var i = 0; i < amt.length; i++){
if(moment(start[i]) <= date && moment(end[i]) >= date){
sum += amt[i] || 0;
}
};
sumRainer
-
Hi Rainier,
Thank you for your help, but it's still showing "0" under Revenue Calculation. What information can I provide you to get a resolution? Also, can Podio search the entire Sales App without having to link each individual sale item from the Sales App in the Revenue Link field in the P&L App?
-
Rainer,
I have a monthly report app and I have an income and expense app. I want to sum the income for the date specified in the report (2 separate date fields, one is the report start and the other report end). I will do the same for the expenses. Any help is appreciated, or even if you link me to the solution, thanks again!
Please sign in to leave a comment.
Comments
7 comments