remove last call , and First call next day

Question

Hi

I have Following data set, I would like to remove last call of that particular day and first call next day

01-03-2021 17:4501-03-2021 17:4901-03-2021 17:5001-03-2021 17:5402-03-2021 09:0802-03-2021 09:1202-03-2021 09:2102-03-2021 09:34

I have Formula Tool and separated date and Time later I have used Multi row Formula

IF [Date] = [Row-1:Date] AND [Time] < [Row-1:Time]
THEN ToDateTime([Start of Call DateTime]) - ToDateTime([Row-1:Start of Call DateTime])
ELSE NULL()
ENDIF

Error: Invalid type in subtraction operator

Qiu · Accepted Answer

@BRRLL99 
I think there must be a better way. 😁

Yoshiro_Fujimori · Accepted Answer

@BRRLL99 @Qiu

My solution 😉

The "particular day" needs to be set in the Filter tool.

Qiu · Accepted Answer

@Yoshiro_Fujimori 
Good one.

We can also do the decending order 😁

binu_acs · Accepted Answer

@BRRLL99 One way to do this with the Multi-Row formula

1. Convert the Date field into the Date format using the DateTimeParse() function

2. Create a flag based on the date difference between the adjacent rows (formula given below)

3. Filter out the flag

IIF(isNull([Row-1:Date1]) OR isNull([Row+1:Date1]),0,IIF(DateTimeDiff([Date1],[Row-1:Date1],'day') = DateTimeDiff([Row+1:Date1],[Date1],'day'),0,1))