Search code examples
spotfire

Spotfire Real-Time Calculations

Trying to see if there's a good way to overcome real-time data sets, where there are differences in time. An example of my data looks like this:

Date-Time Stamp           Depth      Total Time Worked (Hrs)

01/01/2023 12:00am      1000      0
01/01/2023 12:01am      1009      .016
01/01/2023 12:02am      1012      .033
.
.
.
01/04/2023 12:00am      1100      72
01/04/2023 12:01am      1108      72.016
.
.
.

It can be assumed that the lines with dots represent on-going work for the timeframe. The issue is the Total Time. The work was completed for the day on 01/01/2023, but they returned back to work on 01/04/2023. I have a column that is doing the DateDiff between a "Start Time" and "End Time" column, but I'm not exactly sure how I can subtract the difference between the real time worked vs the down time. Does anyone have any ideas or suggestions?

Thanks!

Example

Solution

  • So, from my understanding, the column you want to calculate would give you the running time actually worked. You then can add it up as a KPI or cross table or an extra calculated column if you wish.

    If there are no extra grouping variables in your actual data, and assuming you calculate the time in some defined unit (here I am using minutes but I suspect it is not) then you could do this:

    1 - create a standard date difference column, with respect to the previous time. I am assuming the data is already in the right order (if not, you might have to introduce an intermediate row_id variable). Say this column will be called [Time Gap]:

    SN(DateDiff('minute',Max([Date-Time Stamp]) over (previous([Date-Time Stamp])),[Date-Time Stamp]),0.0)

    SN(..,0.0) handles the first row, where there is no previous timestamp.

    2 - calculate the total time worked [Time Worked] by substituting [Time Gap] with 0.0 every time it exceeds a certain value (here using 1000 as an example)

    case  when [Time Gap]<1000 then [Time Gap] else 0.0 end

    3 - to calculate (for instance) the cumulative time worked as a column:

    Sum([Time Worked]) OVER (AllPrevious([Date-Time Stamp]))