4 Replies Latest reply on Apr 4, 2018 3:49 AM by jagadeesh T

# Count distinct record if longer than 120mins

Hi folks,

my sample dataset

 Ticket number Open Time 1 04/03/2018 12:11 2 04/04/2018 15:26 2 04/04/2018 15:26 3 04/04/2018 16:26 4 04/04/2018 17:49 4 04/04/2018 17:49 5 04/04/2018 21:30

Assuming the time now is 04/042018 22:00

I would like to count the distinct number of tickets which had exceeded 2 hours of 'Open Time'

In this case, it should gives a result of 4 distinct tickets (because ticket 1,2,3,4 had exceeded 2 hours of 'Open Time')

Ticket number '5' is not matched because it has not exceeded 2 hours of 'Open Time'

• ###### 1. Re: Count distinct record if longer than 120mins

Hi Kelvin,

This should help.

Put this to filter and set true.

~Tushar

• ###### 2. Re: Count distinct record if longer than 120mins

Try this logic, Solution is also attached

Ticket(Within two hrs)

IF DATE([Open Time])=DATE(TODAY())

THEN

IF DATEDIFF('hour',[Open Time],NOW())<2

THEN [Ticket number]

END

END

For Distinct Count create another calculated field

COUNTD(Ticket(Within two hrs))

2 of 2 people found this helpful
• ###### 3. Re: Count distinct record if longer than 120mins

Hi Kelvin,

please use the below calculation and select 'true' it will filter all the recorded

Please find the attached workbook and let me know it helps@ you.

Thanks,

• ###### 4. Re: Count distinct record if longer than 120mins

Hi Kelvin,

Please find the attached workbook, i have fixed the current time and done the calculation.

Thanks,