### Post

3 followers Follow

# 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

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()+1var 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()+1var lastyear=moment(@Last Year End).year()moment(@Date1).year()==lastyear?@Money1:0+moment(@Date2).year()==lastyear?@Money2:0+...etc`
Andrew Wardle 1 vote

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

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

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

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

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)`

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

`var thisyear=moment(@Last year end).year()+1var result=0result+=(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)`