Question

Calculate the sum of fields in a table

Forms

Updated December 13, 2022

asked on July 16, 2019

I have a table with [Date From], [Date to] and [Duration] fields.

The [Duration] field calculates the difference between the two date fields. It works fine however, If I add a new row in the table, I get an error message in [Duration] field in the new row. The error message disappears once I fill the values of the date fields in the new row.

I have two questions:

1. How can I prevent this error from showing up?

2. Outside the table, I have a field called [Overall Duration]. I want to calculate the sum of [Duration] in the table rows. I tried =SUM(TableVariable.Duration) but it did not work. I did not get any error just the value Zero. How can I achieve this?

Thanks.

0 0

Answer

SELECTED ANSWER

replied on July 18, 2019 • Show version history

Changing Duration to a Number should resolve the problem with SUM (just keep in mind that it will create a new variable).

To address the other error, you need to include logic that will prevent it from trying to calculate if the fields are empty.

For example,

=IF(AND(INDEX(TableVariable.Date_From,ROW())<>"",INDEX(TableVariable.Date_To,ROW())<>""),DATEDIF(INDEX(TableVariable.Date_From,ROW()),INDEX(TableVariable.Date_To,ROW()),"d"),0)

Basically, you want

IF From is not empty AND To is not empty

THEN DATEDIF

ELSE 0

0 0

Replies

replied on July 16, 2019

How do you have the calculations set up in the table?

For table rows, you need to use the INDEX and ROW functions, which would explain why you're getting an error when you add another row.

For example, INDEX(Table.Column,ROW()) would reference the selected column for the current row.

For the SUM issue, are you using a Number type field for the Duration?

0 0

replied on July 18, 2019 • Show version history

Hi Jason,

Thanks for your response.

I am using the following formula in the for [Duration] in the table rows:

=DATEDIF(INDEX(TableVariable.Date_From,ROW()),INDEX(TableVariable.Date_To,ROW()),"d")

The file type is Single Line. Should I change it to Number type?

Thanks

0 0

SELECTED ANSWER

replied on July 18, 2019 • Show version history

Changing Duration to a Number should resolve the problem with SUM (just keep in mind that it will create a new variable).

To address the other error, you need to include logic that will prevent it from trying to calculate if the fields are empty.

For example,

=IF(AND(INDEX(TableVariable.Date_From,ROW())<>"",INDEX(TableVariable.Date_To,ROW())<>""),DATEDIF(INDEX(TableVariable.Date_From,ROW()),INDEX(TableVariable.Date_To,ROW()),"d"),0)

Basically, you want

IF From is not empty AND To is not empty

THEN DATEDIF

ELSE 0

0 0

replied on July 18, 2019

It worked. Thank you very much!

0 0

replied on December 13, 2022

i want to sum up the values in a column table to the outside single field

0 0

You are not allowed to follow up in this post.

Question

Question

Calculate the sum of fields in a table

Answer

Replies

Sign in to reply to this post.