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

[Tiffany Lai]

Hello,

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.

[Michael Kalada 2]

just the screenshot makes it tough to diagnose this one.  A dxp with the time data and your calculated columns would make it much easier

[Richard Pobi]

I think you have two more parentheses you do not need. Try the below expression.

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

[Tiffany Lai]

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

[Tiffany Lai]

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.

[Tiffany Lai]

Thank you for your reply michael.kalada. I managed to resolve the question myself, please see response below.