Search code examples
spotfire

Calculate time difference based on ID in Spotfire

Hi Everyone, I am new to Spotfire so if someone could point me to the right direction, that would be great. I have a request ID (primary key) that has various statuses and would like to find the difference in time for some specific statuses (say, request submitted vs request approved). Along with that, I want to take the non-working day (weekends off) from the calculation (if request was submitted on Friday and approved on next Monday, then the difference should be just 1 day rather than 3).

My data table is structured as below:

Data Table

Any help/direction will be much appreciated!

Thank you,

Update

The output I am expecting would be something like below. For the ID. 101, the submission date is April 26 and Approval date is May 1st, so we are taking off weekends from the calculation and similarly for ID 103, the request was completed on weekends so time spend was 0 whereas for 102, we calculated the total time elapsed weekdays.

Output

Solution

  • There are two parts of the problem:

    1. match dates from different rows

    Insert a new calculation column ([date approved])
    Max(If([Payment_status_code]="STS_APPROVED",[CREATE_TIMESTAMP],null)) OVER ([PAYMENT_REQUEST_ID])
    This will add approved date to each row.

    2. calculate number of working days

    (I've just copied formula between # from here, replace column names as appropriate)

    If([Payment_status_code]="STS_RECEIVED",#if(dayofyear([START DATE])=dayofyear([END DATE]),0,(Integer(DateDiff("day",[START DATE],[END DATE]) / 7) * 5) + DayOfWeek([END DATE]) - DayOfWeek([START DATE]) + (if((DayOfWeek([END DATE]) - DayOfWeek([START DATE]))<0,5,0)))#,null)