Interval Between Two Dates

[kish87]

Dear Folks,

I need to display time difference b/w two dates with time stamps as shown below.

Am able to calculate TIME DIFF using Interval() as shown below

If(ACTIONBY=Above(ACTIONBY) AND Above(ACTIVITY)=’ Review’, Interval(ACT_DATE)-Above(ACT_DATE)))

I'm not including null's in below table to eliminate dates with nothing

TRANID	ACTIONBY	ACT_DATE	TIME DIFF
121500922	u1234	22-02-2021 11:01:27	00:04:20
121500922	u1234	01-03-2021  14:18:51	00:07:55

Now when I Add the other dimension ACTIVITY / ACTION i cannot see TIME DIFF value in the table   

actual table looks like below

TRANID	ACTIONBY	ACTIVITY	ACTION	ACT_DATE
121500922	u1234	Review	Open	22-02-2021 11:01:27
121500922	u1234	Credit Review	Modify	22-02-2021 11:04:47
121500922	u1234	Review	Open	01-03-2021  14:18:51
121500922	u1234	Credit Review	Reject	01-03-2021  14:26:46

Can someone assist me on this?

Thanks In Advance. 

[Or · Accepted solution]

First, your sample data is a bit of a mess. You've got mixed date formats, an extra space in ACT_DATE for two values, and your ACTIONBY isn't the same user (uppercase U in the last one).

Assuming that's just a sample and not the actual data, though, it seems like within the context of your table ABOVE(Field) doesn't return anything, so you should be using Above(total Field):

If(ACTIONBY=Above(total ACTIONBY) AND Above(total ACTIVITY)= 'Review', Interval(ACT_DATE)-Above(total ACT_DATE))

[kish87]

Thank You!  Data remains Same  but manual  entry caused this mess so corrected and reposted it.

[kish87]

Dear,  Could you assist me with this to bring a derived field in backend please.  

Thanks!

[Or]

I don't understand the question...

If you mean you want to pre-calculate it in the script, you'll need to do it with some sort of Peek() or Previous() situation (and you'll need to make sure your data is pre-sorted in the query / load). Have a look at those functions and see if you can get it to work, there's plenty of examples in Community for using those functions.