How to sum up different number fields when each has its own "if"-field

Answered

Dorothee Bienefeld

September 25, 2015 11:17

Hi all,

I tried to solve the problem for hours, but I totally failed and hope you can help me now. :(

I have an app with different number fields, each has its "own" date field:

number field 1
date field 1
number field 2
date field 2
.....
number field 12
date field 12

Now, I'd like to sum up the number fields, that have date fields, which match a certain month e.g. June.

I thought of creating additional calculation fields for each month in the same app, where I can sum up the month results for later use.

Like:
If date field 1 is in June add number field 1
AND
if date field 2 is in June add number field 2
AND
....

Is there a JavaScript-Code I can use for? I tried a version of https://help.podio.com/hc/communities/public/questions/203827978-IF-date-is-between-X-and-Y-then-SUM-?locale=en-us this code, but can't figure out the right way.

Thanks in advance!
Doro

Comments

12 comments

Rainer Grabowski

September 25, 2015 16:07
Hi Doro,

you need one additional field where you can define which month should be matched. Best is a category field with 12 categories.
The calculation should look like this:
```
var select = @category field;
var n1 = @number field 1;
var d1 = moment(@date field 1).format("MMM");  // "MMM" = Jun , "MMMM" = June 
var n2 = @number field 2;
var d2 = moment(@date field 2).format("MMM");
(and so on for all 12 fields) 

select == d1 ? n1 : select = d2 ? n2 : ... and so on
```
Read as: IF selected value = value d1 THEN show value n1 IF ELSE selected value = d2 show n2. The formula must end with `: value (or : "")

Rainer
rg@delos-consulting.com
1

Comment actions Permalink
Dorothee Bienefeld

September 25, 2015 16:43
Thank you Rainer for your quick answer! and I'm sorry for my request, but this is the first time I work with JS.

So, in case I would have only two pairs of fields and need to know the sum of the number fields which date fields are in June, I would have to create a category field with category "Jun" and the code would look like this?:

var select = @category field;
var n1 = @number field 1;
var d1 = moment(@date field 1).format("Jun");

var n2 = @number field 2;
var d2 = moment(@date field 2).format("Jun");

select == d1 ? n1 : select = d2 ? n2 :
`: value (or : "")

Sorry, but I'm a absolute beginner. Thanks in advance!

PS: How do the category fields look like if I need months in a special year e.g. June 2016?
0

Comment actions Permalink
Rainer Grabowski

September 27, 2015 14:25
You need one field where you check against which number/date pairs you want to get.
This does the var select. It could be a category field, but I can also be a text field. Categories you have to define in the template creator for every month you want to check. If you want all 24 month for the next 2 years you have to create 24 categories. Not so nice. Text fields are more flexible, you need only one. But the problem with text field is, if someone mistypes - cause the entry in the text field must always be exactly the same as the variable dx. If the value in the
```
var d1 = moment(@date field 1).format("MMMM YYYY")
```
(which is = June 2016) and someone enters "Jun 2016" into the text field, it won't match.

If you want to take category fields, one category hast to be "June 2016" and the d-variable has to be:
```
var d1 = moment(@date field 1).format("MMMM YYYY")
```
You can also take a date field (eg. called "Select Month") as var select. E.g. if date 1 is "15. June 2016" and you want to see the connected number, you could enter each day of the Juni 2016 into the Select Month field. But the following code works only correctly, if there is always only one date field d with a June 2016 date, if 2 var d can have a June 2016 value it doesn't work:
```
var select = moment(@Select Month).format("MMYYYY"); 
var n1 = @number field 1;    
var d1 = moment(@date field 1).format("MMYYYY");
...
select == d1 ? n1 : select = d2 ? n2 : ... and so on
```
If there can be muliple date fields with a June 2016 date (e.g. d1 and d2 both can have a June 2016 value) you have to modifiy the calculation:
```
var select = moment(@Select Month).format("MMYYYY"); 
var d1 = moment(@date field 1).format("MMYYYY")  ==  select  ?  @number field 1 : "";
var d2 = moment(@date field 2).format("MMYYYY")  ==  select  ?  @number field 2 : "";
...
```
d1 + "\n" + d2 ....

If both have a June 2016 value this would give you a list of the number values like:
1000
2000

And this
```
d1 + d2
```
would add the values:
3000

The code really depends on your set up and what you want to achieve or what should be shown in the calculation field.

Rainer
rg@delos-consulting.com
0

Comment actions Permalink
Dorothee Bienefeld

September 28, 2015 09:29
Thank you Rainer for your patience!

And I'm sure, I'll use this code too in some other cases. In this one, I really need a calculation field for every month of the next two years, because I need to sum up this exactly field for every item in the app later and it has to be shown constantly (later in a gauge). But the app is part of a background workspace, so no one will notice.

Each item contains between 1 and 12 number/date pairs, but one month can only exist in one field. The problem is, I can`t forecast in which of the twelve fields will be the value for january or february and so on.... This is why I thought of sum up the values, so if there is one value for january

Does this help you understand my problem? And If you do, would you be so kind as to post the code's end exactly again?

I`m overgrateful for your help!
Doro
0

Comment actions Permalink
Rainer Grabowski

September 28, 2015 11:43
Hi,
so you need 24 calculation fields (e.g., Sept 2015, Oct 2015 ... Aug 2017), but you have only 12 date fields? Is that correct? And when all 12 date fields have a value, only 12 ot the 24 calculation fields will have a value?
0

Comment actions Permalink
Dorothee Bienefeld

September 28, 2015 11:56
Yes, this is right. To explain the reason: Our invoices can be divided into a maximum of 12 partial invoices. Each has to be free editable in it's value and date. Later the planned amount of income will be display on a dashboard app.

Sorry, could have explained it earlier...
0

Comment actions Permalink
Rainer Grabowski

September 28, 2015 12:39
Hi Doro,

for that you don't need a var select, but it's much (typing) work (but most you can copy & paste). For each calculation field you can use this (this is an example for the calculation field "September 2015")
```
 var month = "1509";
 var n1 = @number field 1;
 var d1 = moment(@date field 1).format("YYMM""); 
 var n2 = @number field 2;
 var d2 = moment(@date field 2).format("YYMM");
 (and so on for all 12 fields) 

 month == d1 ? n1 : month = d2 ? n2  ... : month = d12 ? n12 : ""
```
"" means: Show nothing (field is empty). If you want 0 instead change the end to : 0; so you would have in each calc field which doesn't match a month "" or 0. For October 2015 var month = "1510". Each calc field is fixed to a dedicated month (I assume, the month will be the field name).

Rainer
0

Comment actions Permalink
Dorothee Bienefeld

September 29, 2015 12:48
Hallo Rainer,

thank you for your help. I inserted the code in fields from October 2015 to March 2015 (changing the var month) and typed in the following test values:
1. December 2015 -> number 50
Result:

October 2015:
November 2015:
December 2015: 50
January 2016:
February 2016:
March 2016:

So far so good...

Now, I typed in a second date/number pair
March 2016 -> number 30

Result:

October 2015: 30
November 2015: 30
December 2015: 50
January 2016: 30
February 2016: 30
March 2016: 30

Do you have any idea what my mistake is?

Thanks in advance!
Doro
0

Comment actions Permalink
Dorothee Bienefeld

September 29, 2015 13:37
Hallo again,

I found the problem myself! Thank you Rainer for your first-class help!

Doro
0

Comment actions Permalink
Dorothee Bienefeld

November 03, 2015 09:35
Hi Rainer,

I'm sorry for asking you again. The code worked really well until one of our employees "found a way" to add two dates of October 2015.

Now, I tried to adjust your code and tried the following:

var month = "1510";

var d1 = moment(@Rechnungsdatum 1).format("YYMM") == select ? @Teilbetrag 1 : "";
var d2 = moment(@Rechnungsdatum 2).format("YYMM") == select ? @Teilbetrag 2 : "";
var d3 = moment(@Rechnungsdatum 3).format("YYMM") == select ? @Teilbetrag 3 : "";
var d4 = moment(@Rechnungsdatum 4).format("YYMM") == select ? @Teilbetrag 4 : "";
var d5 = moment(@Rechnungsdatum 5).format("YYMM") == select ? @Teilbetrag 5 : "";
var d6 = moment(@Rechnungsdatum 6).format("YYMM") == select ? @Teilbetrag 6 : "";
var d7 = moment(@Rechnungsdatum 7).format("YYMM") == select ? @Teilbetrag 7 : "";
var d8 = moment(@Rechnungsdatum 8).format("YYMM") == select ? @Teilbetrag 8 : "";
var d9 = moment(@Rechnungsdatum 9).format("YYMM") == select ? @Teilbetrag 9 : "";
var d10 = moment(@Rechnungsdatum 10).format("YYMM") == select ? @Teilbetrag 10 : "";
var d11 = moment(@Rechnungsdatum 11).format("YYMM") == select ? @Teilbetrag 11 : "";
var d12 = moment(@Rechnungsdatum 12).format("YYMM") == select ? @Teilbetrag 12 : "";

d1 + "\n" + d2 + "\n" + d3 + "\n" + d4 + "\n" + d5 + "\n" + d6 + "\n" + d7 + "\n" + d8 + "\n" + d9 + "\n" + d10 + "\n" + d11 + "\n" + d12 + "\n"

But I get the note "select is not defined"...could you please rescue me again?
0

Comment actions Permalink
Rainer Grabowski

November 03, 2015 10:41
Hi Doro,

if this the complete code in your calculation field, it's not defined cause you don't have a variable select in your code. Rename var month to var select and it will work.

Rainer
0

Comment actions Permalink
Dorothee Bienefeld

November 03, 2015 11:03
Perfect! Thank you again!
0

Comment actions Permalink

Please sign in to leave a comment.

Comments

Didn't find what you were looking for?