Post

3 followers Follow
0
Avatar

Calculate sum of money filtered by date

Hi,

If anyone could give me a hand with this I'd really appreciate it. All of this in in the same app. I am trying to add up 5 different money fields filtered each by date. I also have 5 different field dates. I.e. #1 Invoice value, #1 Invoice Date, #2 Invoice value, #2 Invoice Date, #3 Invoice value, #3 Invoice Date, #4 Invoice value, #4 Invoice Date, #5 Invoice value, #5 Invoice Date. I need a calculation field that adds invoice #1,#2,#3,#4,#5 values IF #1,#2,#3,#4,#5 dates are in the previous year. I would also like to add a second calculation field that adds the values of the current year. How would I go about setting up this up? Many thanks in advance

Sasha Aguilera

Please sign in to leave a comment.

10 comments

1
Avatar

Sasha

Its an odd structure, you might be better with a second app holding transactions linked to your first app, but as it stands this should do it. You obvioously need to know the current date to identify the current year somehow (see comments here) but assuming you just have a "Last Year End" field on your app item:

Invoice total for this year

var thisyear=moment(@Last Year End).year()+1
var lastyear=moment(@Last Year End).year()

moment(@Date1).year()==thisyear?@Money1:0+
moment(@Date2).year()==thisyear?@Money2:0+...etc

Invoice total for last year

var thisyear=moment(@Last Year End).year()+1
var lastyear=moment(@Last Year End).year()

moment(@Date1).year()==lastyear?@Money1:0+
moment(@Date2).year()==lastyear?@Money2:0+...etc
Andrew Wardle 1 vote
Comment actions Permalink
0
Avatar

Thanks Andrew but I must be doing something wrong since I am receiving a 'Script syntax error. Unexpected end of input'. This is what I have for 'Invoice total for this year', where my 'Last year end' is  date field dated Dec 31, 2018

var thisyear=moment(@Last year end).year()+1
var lastyear=moment(@Last year end).year()

moment(@#1 Invoice Date).year()==thisyear?@#1 Invoice value:0+
moment(@#2 Invoice Date).year()==thisyear?@#2 Invoice value:0+
moment(@#3 Invoice Date).year()==thisyear?@#3 Invoice value:0+
moment(@#4 Invoice Date).year()==thisyear?@#4 Invoice value:0+
moment(@#5 Invoice Date).year()==thisyear?@#5 Invoice value:0+

Where is it falling apart?  Many thanks

Sasha Aguilera 0 votes
Comment actions Permalink
0
Avatar

Hi Sasha,

remove the last + 
In the field for the current year sum you need var thisyear only, in the field for last year sum var lastyear only,
Btw: That solution with the Last year end field works only if the invoice dates are in 2018 and 2019. If e.g invoice 5 is in 2020 you need to change the Last year end field. 

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Originally I had tried that without success....yet this time it worked! Thank you Andrew and Rainer.

Sasha Aguilera 0 votes
Comment actions Permalink
0
Avatar

Hi, Unfortunately there seems to be an issue and I don't if it just happened all of a sudden somehow or if I didn't notice before since I didn't have multiple invoices in the same year.

var thisyear=moment(@Last year end).year()+1
moment(@#1 Invoice Date).year()==thisyear?@#1 Invoice value:0+
moment(@#2 Invoice Date).year()==thisyear?@#2 Invoice value:0+
moment(@#3 Invoice Date).year()==thisyear?@#3 Invoice value:0+
moment(@#4 Invoice Date).year()==thisyear?@#4 Invoice value:0+
moment(@#5 Invoice Date).year()==thisyear?@#5 Invoice value:0

The result is the total of Invoice value 1 only, not of Invoice value 1 AND 2. There are no other invoice values but 1 and 2. Both invoice 1 and 2 are dated 2019. Any ideas? Thank you!

 

 

Sasha Aguilera 0 votes
Comment actions Permalink
0
Avatar

Sasha

If you put the conditionals in brackets like this does it help?

var thisyear=moment(@Last year end).year()+1
(moment(@#1 Invoice Date).year()==thisyear?@#1 Invoice value:0)+
(moment(@#2 Invoice Date).year()==thisyear?@#2 Invoice value:0)+
(moment(@#3 Invoice Date).year()==thisyear?@#3 Invoice value:0)+
(moment(@#4 Invoice Date).year()==thisyear?@#4 Invoice value:0)+
(moment(@#5 Invoice Date).year()==thisyear?@#5 Invoice value:0)
Andrew Wardle 0 votes
Comment actions Permalink
0
Avatar

Thanks Andrew, but it doesn't help unfortunately. It now it give me a "Script error: TypeError: number is not a function"

var thisyear=moment(@Last year end).year()+1
(moment(@#1 Invoice Date).year()==thisyear?@#1 Invoice value):0+
(moment(@#2 Invoice Date).year()==thisyear?@#2 Invoice value):0+
(moment(@#3 Invoice Date).year()==thisyear?@#3 Invoice value):0+
(moment(@#4 Invoice Date).year()==thisyear?@#4 Invoice value):0+
(moment(@#5 Invoice Date).year()==thisyear?@#5 Invoice value):0

Sasha Aguilera 0 votes
Comment actions Permalink
0
Avatar

Strange. This will force it to do the addition I think

var thisyear=moment(@Last year end).year()+1
var result=0

result+=(moment(@#1 Invoice Date).year()==thisyear?@#1 Invoice value:0)
result+=(moment(@#2 Invoice Date).year()==thisyear?@#2 Invoice value:0)
result+=(moment(@#3 Invoice Date).year()==thisyear?@#3 Invoice value:0)
result+=(moment(@#4 Invoice Date).year()==thisyear?@#4 Invoice value:0)
result+=(moment(@#5 Invoice Date).year()==thisyear?@#5 Invoice value:0)
Andrew Wardle 0 votes
Comment actions Permalink