Jump to content

Error in calculated column output - cumulative time elapsed using single column of date/time data

Tiffany Lai

Recommended Posts


I am experiencing an odd error with the values of a calculated column, not with the code itself. I have created a calculated column that shows the amount of time elapsed:

1. To create calculated column called "Time elapsed": If([amount]>=0,DateDiff("minute",Min([Timestamp]) OVER (Previous([Timestamp])),[Timestamp]))

2. Then to create a calculated column that shows the cumulative time elapsed:Sum([Time elapsed]) over (Intersect([Time elapsed],AllPrevious([RowId()])))

This works for the first 21 rows, but displays the wrong elapsed time after that (the cumulative time elapsed resets itself). Please see screenshot for details.

Any idea why this might be happening Not sure if there is something wrong with my code, or with Spotfire. Thank you.

Link to comment
Share on other sites

Thanks richierich, this seems to solve the problem of the 2nd calculated column which converts the first calculated column to minutes. However, the first calculated column is still showing the wrong values:


If([amount]>=0,DateDiff("minute",Min([Timestamp]) OVER (Previous([Timestamp])),[Timestamp]))

Link to comment
Share on other sites

I think I answered my own question

First, create a column using the RowId() function.

Then, the following expression will create a calculated column that finds the time difference between the current and previous row (or node):

If([amount]>=0, DateDiff("minute",Max([CH1 Time]) OVER (Previous([RowId()])),[CH1 Time]))

The next expression is for the final calculated column which will provide a cumulate sum of the time elapsed. Removing the intersect function seemed to have solved the problem.

Sum([Time elapsed]) over AllPrevious([RowId()])

Thanks richierich for pointing out the extra parentheses.

Hopefully this will be helpful to anyone else experiencing the same problem.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...