remove last call , and First call next day

[BRRLL99]

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:45

01-03-2021 17:49

01-03-2021 17:50

01-03-2021 17:54

02-03-2021 09:08

02-03-2021 09:12

02-03-2021 09:21

02-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 solution]

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

[Yoshiro_Fujimori · Accepted solution]

@BRRLL99 @Qiu 

My solution 😉

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

[Qiu · Accepted solution]

@Yoshiro_Fujimori 
Good one.

We can also do the decending order 😁

[binuacs · Accepted solution]

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

[BRRLL99]

you have changed date time format into date format

how can we know it is the last call for that particular day and first call the next day? 

[binuacs]

@BRRLL99 I updated the workflow to sort the dates in ascending order, in that way you will see the last call of the particular day and the first call on the next day