Calculating Total Sales Based on Date

Hi Chris,

var num must be @all of not @sum of, also you need to parse date[i] to moment() - this should work:

var date = @All of Date of Invoice; // date field in App A
var num = @All of ex VAT Total; // number field in App A
var startFixed = moment("2015/01/01","YYYY/MM/DD").unix(); // start date of time span
var endFixed = moment("2018/12/31","YYYY/MM/DD").unix(); // end date of time span
totalFixed = 0;
for(var i = 0; i < num.length; i++) {
if (moment(date[i]).unix() >= startFixed && moment(date[i]).unix() <= endFixed) {
totalFixed  += (num[i] || 0);
}
}
totalFixed ;

Your "ideal" solution (rolling basis) is rather easy to implement. If you use Globiflow it's comfortably too, without Globiflow it's a bit cumbersome. 

Rainer

Hi Rainer,

I do use globiflow, Can you confirm what you think the easy solution is for the rolling basis?

thanks again, chris

Hello Chris,

the calculation field doesn't know a value "today" - there's no function or built-in variable which returns today's date and updates it each midnight. Means: You've to create your own variable "Today's Date".  All of my clients (I'm a Podio/Globiflow consultant) have it. 

Create an app called "Timer", it needs 2 fields only, the title and a date field (start date only, name it "Current Date"). The app can be in whatever workspace. 
Then create one item, enter "Today" as title and the current date in the date field. 

Next: Refresh Globiflow and create a "By Date or Day" flow (at the top of the workspace tree):
Everday starting at 00:15
(remove any filter)
Search for items in app "Timer"
where field Title is equal to "Today"
update collected in App "Timer"
date field = Value [current date]

Now you've one item which always holds the current date.

In the app where you want to do the calculation add a relationship field which points to the app "Timer" (call the field e.g. "Today's Date"). 
Each item in that app must be related to the Timer item. You can do that manually or - more comfortable - by a flow. 
First you need to connect all existing items. 
Refresh Globiflow and create a Manual triggered Flow in that app:
Update item in field
Today's Date = App Item ID in app Timer App Item ID = 1
Save the flow, click Run (in the dropdown besides the Edit button), enter 0 and run. 
Now all existing items are connected to the Timer item.
Edit the flow, change the trigger from Manually to When item created. Save the flow (all future items will be connected automatically). 

In the calculation field:

var date = @All of Date of Invoice; // date field in App A
var num = @All of ex VAT Total; // number field in App A
var today = @Max of Current Date;
var start = moment(today).subtract(365,"days").unix(); // start date of time span
var end = moment(today).unix(); // end date of time span
total = 0;
for(var i = 0; i < num.length; i++) {
if (moment(date[i]).unix() >= start && moment(date[i]).unix() <= end) {
total  += (num[i] || 0);
}
}
total;

Rainer