4 Replies Latest reply on Aug 29, 2018 12:41 PM by christopher berger

# Time difference between rows

Hi,

I need some assistance with a datediff calculation. Basically, I have the below "Resource IDs" and each "Resource IDs" can enter a "State" at a specific time. Once, the "Resource IDs" enters a "State" it's considered to be in this "State" until a state change occurs. What I need to do is be able to calculate the total time for each "State" for each "Resource IDs". (E.g. IBP03 -> DOWN -> 34min). The calculated time should update as a filter is applied to the "Updated Date" field. I have looked through the forum and tried several different approaches but couldn't get to the right answer. Unfortunately, I can't attached the work book due to confidentiality. Any help is really appreciated.

• ###### 1. Re: Time difference between rows

Hi Chris,

Taken sample data set

Create a calculated field like below

Calculation:

Days Diff :

if INDEX()=1

then

0

ELSE

DATEDIFF('day',LOOKUP(ATTR([Order Date]),-1),ATTR([Order Date]))

END

Replace the order date field with your date field

Once you create the calculation --> Drag it to your view --> Right click on it edit table calculation

Update your table calcualtion like below

Hope this helps kindly mark this answer as correct and helpful so that it will help others

BR,

NB

• ###### 2. Re: Time difference between rows

Thanks Naveen for the quick and good response. This almost worked. The calculation seems to be right but the value is shifted by one row...

• ###### 3. Re: Time difference between rows

LOOKUP(date , 1) it will take the next value and you will get the required O/P

I have taken the Previous value to calculate

Hope this helps

BR,

NB

• ###### 4. Re: Time difference between rows

Great. That worked. I have one more question and then I'll be all set. As a next step I would need to sum all of the calculated times per "State" so I know how long each resource was in a given state. I just realized I can't use LOD with ATTR so now I am stalled again.