Post

2 followers Follow
0
Avatar

Calculating Total Sales Based on Date

I want the below formula to return the sum of ex VAT total field for any sales that fall between the two dates from APP A and the result is put into a field in APP B.

 

Ideally I would have it so that it returned a sum of these sales but on a rolling basis - so taking todays date and including any sales made in the last 365 days. But this is where I have got to so far.

 

var date = @All of Date of Invoice; // date field in App A
var num = @Sum of ex VAT Total; // number field in App A
var startFixed = moment("2015/01/01").toDate(); // start date of time span
var endFixed = moment("2018/12/31").toDate(); // end date of time span

totalFixed = 0;

for(var i = 0; i < num ; i++) {
if (date[i] >= startFixed && date[i] <= endFixed) {
totalFixed  +=num[i];
}
}
totalFixed ;

Chris Wood

Please sign in to leave a comment.

3 comments

0
Avatar

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

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Hi Rainer,

 

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

 

thanks again, chris

Chris Wood 0 votes
Comment actions Permalink
0
Avatar

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

 


Rainer Grabowski 0 votes
Comment actions Permalink